 # #num error when calculating dates

• Cell A2 has the formula =today()

Cells AE2 hasthe formula =EDATE(A2,1)
AF2 has the formula =EDATE(A2,2)
AG2 has the formula =EDATE(A2,3)
ETC.,

Column I contains dates that projects will be completed. The dates are formatted: mmm yyyy

I have other cells that will calculate the number of months between two dates using the formula =MONTH(I9-AE2)

This formula works, unless the months are the same in column I and the column, AE, AF, AG, etc.
If I9 contains the date Dec 2011 and AE2 contains December 2011 (or any other months that are the same) it will result in a #NUM error.

If I manually type in the dates without using a formula in the AE, AF, AG, etc columns it wil work OK. If the dates are from different months the formula will also work.

Any ideas on what is going on here and how to get this to work without the error?

• Re: #num error when calculating dates

in your formula, if I9 is less than AE2, then it will show a NUM# error. Instead, why don't you use. Note, I9 should ideally be greater than AE2

=MONTH(I9)+(12*(YEAR(I9)-YEAR(AEK2)))-MONTH(AE2)

Cheers,

S M C

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!