Microsoft Excel Problem

  Poloman69 19:01 27 Jan 2004

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 2004

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 2004

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

  VoG II 22:01 27 Jan 2004

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

  GroupFC 22:02 27 Jan 2004

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 2004

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 2004

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 2004

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 2004


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

Elsewhere on IDG sites

iPhone X review

How to find a font: Discover the name of a typeface with these apps

The best iPhone for 2017

Comment créer un compte PayPal pour payer en ligne ?