Jackcoms 15:22 15 Sep 2005
Locked

I have two dates. Let's say 18 October 2005 and 18 August 2010.

I want a simple formula that will tell me how many calendar months there are between these two dates.

Yes, I know I could do it in my head, but I need to do it in Excel. :-)

pj123 15:45 15 Sep 2005

Unfortunately, I don't think I will be here in 2010 but I am sure that Vog will come up with the answer.

Jackcoms 15:47 15 Sep 2005

lol.

Yes, I was rather hoping that Vog might be around. ;-)

scotty 16:15 15 Sep 2005

=(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3)

A4 and A3 are the cells containing the two dates to be compared.

This works in Excel 2003. I do not recall these functions in earlier versions so not sure if it will work on older versions.

Jackcoms 16:25 15 Sep 2005

It worked (Excel 2002)!!

Thanks very much. :-))

pj123 16:32 15 Sep 2005

scotty, just tried it in Excel 2000 and got 59 so looks good to me.

Jackcoms 16:39 15 Sep 2005

Oddly enough, using my above dates, I got 58.

But that's good enough for my purpose

VoG II 15:19 16 Sep 2005

For future reference

=DATEDIF(A1,B1,"m")

where A1 is the first date and B1 is the second.

scotty 15:39 16 Sep 2005

Grrr!! VoG is such a pain, he always has a better way!!

No, seriously, this is an excellent formula and you can replace m with d or y to get days or years. However, when I read VoG's suggestion I looked for this function in Excel but I cannot find it listed. Why is this hidden and how do I see such undocumented functions?

VoG II 15:47 16 Sep 2005

Peter Noneley's Excel Function Dictionary click here right at the bottom of the page.

scotty 15:55 16 Sep 2005

Ah - the second link explains the lack of documentation. Thanks for that VoG.

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

Elsewhere on IDG sites

Alienware 17 R4 2017 review

These brilliant Lego posters show just what children's imaginations are capable of

Mac power user tips and hidden tricks

Comment rĂ©initialiser votre PC, ordinateur portable ou tablette Windows ?