Odd Time result

Over the years, in different versions of Excel there have sometimes been issues within the "md" version of DATEDIF (returning negative numbers in some cases), so this would just appear to be a way to avoid using DATEDIF with "md".......but it's pretty meaningless as that part references B2 date only, so it will always show the day of B2-1.

This version will only work reliably if A2 is always the first of the month

In the past to avoid problems with "md" I've used this formula for the days part

=B2-EDATE(A2,DATEDIF(A2,B2,"m"))&" days"

For example, this formula returns -2

=DATEDIF(DATE(2017,5,31),DATE(2018,3,1),"md")

whereas

=DATE(2018,3,1)-EDATE(DATE(2017,5,31),DATEDIF(DATE(2017,5,31),DATE(2018,3,1),"m"))

formatted as number, returns 1

Originally Posted by neil40

so I applied some formulae to work out how many days he'd been alive.

I am assuming you didnt actually mean what you asked for. You stated "days" alive. If thats what you actually meant its simply newer date - older date and format it as a number/general. Ex: 11/7/1918 - 11/7/2018 = x days (B2 - A2).

If you are looking for days, months and years then it gets more complicated. Primarily leap years become a problem but there are other considerations that make this hard to do.

As mentioned some built in functions have quirks and will in some circumstances produce odd results. DATEDIF is (or at least was) an undocumented function for a long time. As such while it is available to use I never use it as I dont expect it to function properly.

So the question becomes are you looking for years with months/days as a remainder? or are you looking for total years, total months, total days (independent of each other)?

I do know if you want to convert days to years you can do days * 365.25 (365 days in a year, .25 for leap year once ever 4 years). This results in a decimal representation of years. So 1 day short of 100 years would be something like 99.99726.

Months is in particular are a problem as each month isnt the same number of days. Being exact means knowing which months are between 2 dates and accounting for the number of days in each of those months (including if Feb was leap or not). You could instead average # of days in a month in which case your answer for number of months isnt exact then.

In any case if you could clarify the results you are looking for it would make it easier to give an exact answer. Hopefully the prior response solves the matter though

Originally Posted by neil40

I have consistently found that it will be 35,625 days .

I assume that's a typo. In any 100 year period of time (at least since leap years obeyed the current rules) there will either be 36,525 days or 36524 depending on whether the last year of the century is a leap year or not, so for anybody alive today and reaching 100 years old that will be 36,525 because 2000 was a leap year, but 2100 isn't so, for example, between today and the same date in 100 years (16th January 2118) there are 36,524 days.

Just a simple subtraction in Excel will give you that result:

=DATE(2118,1,16)-DATE(2018,1,16)