Excel formula query

  freddy-firecracker 13:19 01 Sep 04
Locked

Hello

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 04

=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 04

VoG™

Nice!

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

  VoG II 14:15 01 Sep 04

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 04

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 04

Thank you for the clarification.

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

Best.

  pc moron 22:06 04 Sep 04

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 04

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

FF

  VoG II 20:01 05 Sep 04

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

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

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

Method Studios' title sequence for BBC series Taboo is truly unsettling

Best Pages for iOS tips | How to use Pages for iPad & iPhone: 6 simple tips to get more out of…