DATEDIF - trouble with February - Excel 07

[RB1645]

Hi

Having a spot of bother with DATEDIF. I'm trying to count the number of full months between two given dates. DATEDIF was doing just fine until I got to February.

I'm using =DATEDIF(Start Date,End Date,"m") and getting the following ...

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]Start Date
[/TD]
[TD]End Date
[/TD]
[TD]DATEDIF result
[/TD]
[/TR]
[TR]
[TD]31/12/2012
[/TD]
[TD]31/1/2013
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]31/1/2013
[/TD]
[TD]28/2/2013
[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]28/2/2013
[/TD]
[TD]30/3/2013
[/TD]
[TD]1
[/TD]
[/TR]
</TBODY>[/TABLE]

Adding a +1 adjustment on any February entries isn't really going to work - as I have a lot of data and this would be asking for trouble later when the tool I'm building is 'released into the wild'.

Any help greatly appreciated.

Thanks very much.

-----

Even better would be a way to count full named months between two dates rather than the DATEDIF method above.

By "full named months" I mean the entirety of a month from first day to last - e.g., January, 1st to 31st inclusive; February 1st to 28th/29th inclusive; etc.

If I wanted to know the number of full months, as defined above, between 15/12/2012 and 31/1/2013 I would want the answer to be 1 (i.e., all of January falls in the specified period). Whereas between 15/12/2012 and 16/1/2013 I'd want 0 (i.e., there are only parts of full named months in the period).

 

[Andrew Poulsom]

You may find this thread interesting:

http://www.mrexcel.com/forum/excel-questions/302028-bug-datedif-excel-2007-a.html

 

[RB1645]

Thanks for that Andrew - I had seen that thread, but not realised there were multiple pages to it - every day's a school day.

It seems it's a problem taxing bigger Excel brains than mine.

It's a shame HR Managers don't write sick leave tracking policies that respect the date calcs that Excel can cope with (or vice versa)! Life would be so much easier.