Microsoft Excel Problem

  Poloman69 19:01 27 Jan 04

Using Microsoft Excel 2000 is there a calculation or formula that i can create that will calculate the total number of 'complete' years and months from a search string


If i enter '1st January 1999' and '2nd December 2003'

it will return a value of

'3years 11 months' - (note the days part is not needed)

Any help would be GREATLY appreciated........

  VoG II 19:15 27 Jan 04

A1 2/12/2003

B1 1/01/1999

D1 =DATEDIF(B1,A1,"m")

which gives you the difference in months.

E1 =INT(D1/12) & " years " & MOD(D1,12) &" months"

which gives 4 years 11 months

  Poloman69 20:15 27 Jan 04

if i wanted to include the days as well - what would i need to do?

  VoG II 22:01 27 Jan 04

=DATEDIF(B1,A1,"y") & " years " & DATEDIF(B1,A1,"ym") & " months " & DATEDIF(B1,A1,"md") & " days"

  GroupFC 22:02 27 Jan 04

For future reference - I have been looking for a solution to this problem for a couple of days now (off and on!).

  Big Elf 22:11 27 Jan 04

For reference too. One of these days an Excel question is going to be asked for which Vog doesn't know the answer.

If that happens it's the end of the world as we know it.

  VoG II 22:18 27 Jan 04

I don't know everything about Excel either. You will see a difference between the methods that I used in my posts timed at 19:15 and 22:01. That's because I don't use date/time functions regularly but when the question about days came up I went and looked up how to use DATEDIF. (This did not take 3 hours btw.)

  Cesar 10:25 28 Jan 04

For questions on Excel I can thoroughly reccomend the Book "Mr Excel on Excel" click here and download the add ons.

  Poloman69 16:39 28 Jan 04


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

Hands-on: Acer Predator Triton 700 review

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

This poetic animation about monsters is illustrated in a Van Gogh painterly style

Apple predictions 2017