Counting the Number of Months

Hi there,

I am trying to calculate the number of months between two given dates.
The spreadsheet is basically about staff and their start and end dates.

For example start dates would be in colum A and end dates would be in column B (in dd/mm/yyyy format).

I am currently using the following formula:
=DATEDIF(A1,B1,"m")

It does return the correct results when the dates are the same e.g. start date = 01/01/2006 and end date = 01/04/2006 = 3 months.
BUT when I change the end date to 31/03/2006, it returns as 2 months.

Is there a way around this? I need a more accurate number of months (with no decimal places)

Thanks guys!

Hi,

Please Login or Register  to view this content.

HTH
Carim

Thanks mate that's quite helpful. The only issue is that it still returns the value 1 if there is no data in the two columns.

Its just that there are other formulas depending on the results and if there's a value there, the depending formulas will not depict the correct information.

Cheers for your help though and let me know if you know a way around this!

Please Login or Register  to view this content.

HTH
Carim

Great Carim. Thanks for all your help!!! just a quick one, is there a way to display the cell value as blank rather than zero? i tried replacing the zero in the formula with "" but it didn't work.

Sorry for the trouble.

Cheers!!!

Hi,

Tools Options View Zero Values

HTH
Carim

mate you've got an answer for everything!!!

THANKS CARIM!!! Highly appreciated!

Cheers

Originally Posted by incognito

It does return the correct results when the dates are the same e.g. start date = 01/01/2006 and end date = 01/04/2006 = 3 months.
BUT when I change the end date to 31/03/2006, it returns as 2 months.

Is there a way around this? I need a more accurate number of months (with no decimal places)

You really need to define what you mean by "a more accurate number of months". Why is 2 months "wrong" in the example you give above?

I don't see how Carim's formula is "more accurate", look at an example

start date 31st jan 2007, end date 1st March 2007,

This formula

=DATEDIF(A2,B2,"m")+(DAY(A2)>DAY(B2))+IF(AND(A2<>"",B2<>""),1,0)

returns 3, yet there are only 29 days between these two dates.

If your start date is 1st December 2006 and end date 31st January 2007 then the formula returns 2 but here there are 61 days between the dates

daddylonglegs you are right. I hadn't tested the formular with the 31st of each month. i was only using the 1st. The formula does give inaccurate answers if these dates are used.

What I am after is to be able to calculate the exact number of months between two date. I need the number rounded up or down so as to get rid of decimals.

As i said in my initial thread, start dates would be in colum A and end dates would be in column B (in dd/mm/yyyy format).

If i use the following formula:
=DATEDIF(A1,B1,"m")

It does returns the following results:

Start date = 01/01/2006 and end date = 01/04/2006 = 3 months.
BUT when I change the end date to 31/03/2006, it returns as 2 months (which is incorrect)

Do you have a solution for this?

Hi,

Forget datedif() and use

Please Login or Register  to view this content.

HTH
Carim

Thanks Carim. That's what I was using initially. I thought there was a better way of doing it. Anyway I have nested the ROUND function into that formula so its not too bad at the moment.

Thanks for your help.

Cheers!!!

Try my version, I think it's more exact...
=DATEDIF(EOMONTH($L16,-1)+1,AH$13,"M")+((DAY(AH$13)-DAY($L16))/DAY(AH$13))

First part is the dated if going from the beginning of the month, then the second part takes the day difference and divides by the original date (maybe it should be the new date) ---------- (new date days-old date days)/old date days (starting date)

Try a friends version, I have not used this formula before. The results varied slightly in a few cases.
=IF($M17<AH$13,YEARFRAC($M17,AH$13)*12,0)