•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Microsoft Excel keeps calling me a liar
Our take
Struggling with Excel's age calculations can be frustrating, especially when it misrepresents something as meaningful as your dad's birthday. If you've entered his birthdate correctly but still see an incorrect age, you're not alone. The formula you've used, =DATEDIF(B6,TODAY(),"Y"), is meant to provide accurate results, but formatting issues might be at play. Let’s dive into the common pitfalls and solutions that can help ensure your family’s ages are displayed accurately—so you can celebrate those milestones without any confusion.
I have a spreadsheet where I keep track of the birthdays and current ages of my family members. For example, my dad’s D.O.B. is 6/9/34. He is 91 (he’ll turn 92 this June) – but Microsoft Excel keeps insisting in the “Age” field (B6) that he’s 89. The formula I have in that cell is as follows:
=DATEDIF(B6,TODAY(),"Y")
(Note: Cell B6 is formatted as a Date. I changed it to General and then back to Date and it made no difference.)
Any ideas?! I’ve been sober for 19 years and that number is gonna change to “about 30 minutes” very soon!
Thanks
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Conditional formatting date help neededI've been trying to use conditional formatting to help automate my work spreadsheet and the date formulas truly escape me. I feel like TODAY is a meany who likes to stick their tongue out at you and point for being stupid XD. This is a spreadsheet with a schedule on it. I am trying to get it to automatically grey out the text when the date passes so I can sort and filter by color and always keep the next upcoming appointment slot be top of the list, while still keeping the data in this sheet because another sheet refers to it via XLOOKUP. https://preview.redd.it/z1jqata8w6xg1.png?width=364&format=png&auto=webp&s=d49f71c8de80c402de1af923fc87e3371d606cc8 Here's the formula I'm using =AND($B$2<TODAY(), $D$2<> "") Column D is client names, for privacy purposes I didn't copy that. They end at D11, if it matters. I'm not sure why excel is treating the dates in May as if they are less than today, when they're not. Does anyone have any ideas? submitted by /u/tashykat [link] [comments]
- Testing suggested methods for calculating ageEarlier today a post (since deleted) asked about a formula for calculating a person's age in whole years. The original poster's (OP's) formula returned an incorrect result in the given example, for reasons that are not clear. Original post: https://www.reddit.com/r/excel/comments/1qux6dg/removed_by_moderator/ Several people suggested alternative formulae. Just for fun, I collected those formulae and analyzed how often they return correct results. The formulae have been standardized (and, in a couple of cases, simplified) to refer to the Date of Birth (DOB) date in A1 and the As At date in B1. Age is defined as incrementing by 1 year on the anniversary of the DOB. Dates can be tricky to work with, so it isn't surprising that the suggested formulae vary in their accuracy. Even so, only 3 of the 11 methods (27%) are 100% accurate. That is: https://preview.redd.it/86423t41qdhg1.png?width=1211&format=png&auto=webp&s=567871746f7654654d5db6e94eb5d8e90ab5b57a The table shows the 11 methods, of which the OP's formula is Method 1. The formulae were tested by generating 100,000 DOB dates from 1 Jan 1900 to 31 Dec 2025 (inclusive) and corresponding "As At" dates from the DOB to 31 Dec 2099 (inclusive). The last four columns show an example where a method produces an incorrect result. Methods 1 to 3 return the correct integer age in 100% of the test cases. Methods 4 to 7 are correct in almost all cases, with a few cases having an error of +/- 1 year. The remaining methods are increasingly inaccurate. Notes: The DATEDIF function is deprecated and unsupported. It has several known bugs (https://bettersolutions.com/excel/functions/function-datedif.htm) given various options, though the "Y" option used by the OP is not known to have bugs. The YEARFRAC function is often promoted as a replacement for some uses of DATEDIF. YEARFRAC mostly works OK in this situation in Methods 4 and 5, except for some edge cases where it returns the wrong age (specifically when the DOB / As At day and month match). None of YEARFRAC's "Basis" parameter values return correct results in all of the tested cases. Basis=4 (European 30/360) has the lowest error rate, at about 2 or 3 errors out of 100,000 cases, though that basis wasn't used in any of the listed methods. submitted by /u/SolverMax [link] [comments]