Excel formula query

  freddy-firecracker 13:19 01 Sep 2004


I am trying to set up a formula which calculates the length of time between two dates and supplies the answer in years and days. The whole date to be shown in one cell.

Any ideas.

  VoG II 13:45 01 Sep 2004

=DATEDIF(B3,C3,"y")&" Y " & DATEDIF(B3,C3,"yd")&"D"

with first date in B3 and second date in C3

  cherria 14:03 01 Sep 2004



I didn't even know that one existed, it does not show anywhere in my help?

  VoG II 14:15 01 Sep 2004

It is an undocumented feature! click here

Nice one VoG™, I had gone for the following:

=INT((B1-A1)/365) & " years " & MOD((B1-A1),365) & " days"

The trouble is that neither caters for the leapyear properly, datedif also throws up some peculiar results such as 3 year 365 days for date 1 being 1 Jan 2000 and date 2 being 31 Dec 2003.

It would have to be a VBA solution to cater for the leap year situation - over to you... :-))))

  VoG II 22:42 03 Sep 2004

Erm, no! It is over to Microsoft. I was under the impression that DATEDIF dealt with leap years. If not, I apologise. I thought that dates worked correctly in Excel, as long as they were post 1900.

There seems to be a misapprehension on this forum that I am some sort of Excel guru. This is not the case. I know a little but not the lot. Especially where dates and times are concerned.

The problem with datedif is that Microsoft have determined that it will use the first entry year to determine whether the 29 days of February in Leapyears should be taken into account.

With the problem posed by freddy-firecracker, it might be a better solution to use Datedif to accurately get the number of years and to use the Julian days element of the last year.

=DATEDIF(A2,B2,"y") & " Years " & B2-DATE(YEAR(B2),1,0) & " Days"

This formula counts the initial and last date in its calculation, if you wish to count the difference between the dates then you would change the zero to a one.

Either will produce an accurate result with regards leapyears.

With further acknowledgements to click here

  VoG II 19:08 04 Sep 2004

Thank you for the clarification.

That is an elegant solution. I wish I had thought of it ... but I will in the future :o)


  pc moron 22:06 04 Sep 2004

Nice one Whisperer.

I'd read the bit about leapyears for the DATEDIF function in the link supplied by VoG™.

Your solution (above) never occurred to me.

  freddy-firecracker 19:48 05 Sep 2004

Following all answers about the previous query re: years and days. say i get a reponse in one cell which says 3 years 126 days, How would i convert the 3 part and the 126 days into another single cell(ignoring the text) so that i can show it as 3.345 thus enabling me to multiply by another figure??

Hopefully you will understand what i mean


  VoG II 20:01 05 Sep 2004

=DATEDIF(A2,B2,"y")+ (B2-DATE(YEAR(B2),1,0))/365

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

HTC U12 Plus review: Hands-on

There's a new Gradient tool coming to Adobe Illustrator

Best Android emulators for Mac

Les drôles de questions à poser à votre assistant Google Home