DATEDIF and Custom format producing different results

[Wirral Wizard]

I have a spreadsheet with lots of event dates on and also people's dates of birth. I have used formulas to work out the age in days of each person at each event. So far, so good.

I then added a simple 'LARGE' formula to find the oldest person at any event. This gave me the answer 12977.

I used Custom Format to convert this result from days into years, months and days and got the answer 35y 7m 12d.

But using DateDif to check the result gives 35y 6m 12d and I can't understand why there is a difference of 1 month. The DateDif result is the correct one but I can't use this to convert the value of 12977 as that could change to a different value in the future.

Why does Custom Format give a different answer? Is there a way round the problem?

[sweep]

what dates are you dealing with to produce the 12977 result?

[Wirral Wizard]

Thanks Sweep. Dates are 4/2/62 and 16/8/97.

[daddylonglegs]

The custom format you used is not appropriate for displaying a duration. That sort of formatting is essentially date based, so for months you will always get a figure 1 to 12, whereas for a duration you need to be in the range 0 to 11 so months always display 1 higher than expected. There's no easy way (or even a difficult way that I know) to do that with formatting

If you want years, months and days you can do that with DATEDIF too, e.g. with DOB in A2 and event date in B2

=DATEDIF(A2,B2,"y")&"y "&DATEDIF(A2,B2,"ym")&"m "&DATEDIF(A2,B2,"md")&"d"

[Wirral Wizard]

Thanks DLL. I did know about getting years, months and days with DATEDIF. But if I use this I can't then use a formula to identify the oldest, youngest etc. etc. (unless you know differently!). Hence the reason for using days only.

[daddylonglegs]

Well, if you have DOBs in A2:A10 then to get the oldest person you need the smallest DOB so you could either use a separate cell with the formula

=MIN(A2:A10) and then reference that cell in the formula (instead of A2) or you could just use that directly in the formula, e.g.

=DATEDIF(MIN(A2:A10),B2,"y")&"y "&DATEDIF(MIN(A2:A10),B2,"ym")&"m "&DATEDIF(MIN(A2:A10),B2,"md")&"d"

......or do you need to get the maximum age across different events? If that's the case then you could just use your previous system to get the maximum number of days and, assuming that's in cell D2 you could use DATEDIF like this

=DATEDIF(0,D2,"y")&"y "&DATEDIF(0,D2,"ym")&"m "&DATEDIF(0,D2,"md")&"d"

[BTW if you only want the difference in days you don't really need DATEDIF at all, you can just subtract the DOB from the event date, i.e. =B2-A2]

[Wirral Wizard]

That last bit gave me the solution DLL, thanks very much for your help.