How would you do this calculation for a range of cells all compared to the
current date but ignoring blank cells? ex: =(L25-M3) works fine (L25
contains the =today() formula to insert the current date) but where column
"N" contains no value it displays an errent number. In other words if there
is no value in column "M" I want no value returned.
"David Biddulph" wrote:
I hope that Dec 2008 - Jan 2006 is 35, rather than 23. :-)
An alternative to DATEDIF would be
=MONTH(A1)-MONTH(B1)+12*(YEAR(A1)-YEAR(B1))
Note that if you have different dates within the start and end months,
you'll get different results from the 2 formulae.
End of Jan to beginning of Feb gives a 1 month difference with the original
=MONTH(A1)-MONTH(B1) and with my extension thereto, but DATEDIF counts
completed months (with various questions when months are of unequal length).
--
David Biddulph
"Ron Rosenfeld" wrote in message
...
On Sun, 30 Dec 2007 18:37:01 -0800, Mike
wrote:
=MONTH(A1)-MONTH(B1)
That works when the two months are in the same year, but what if they are
in
different years? For example January 2008-September 2007 = -8, but should
equal 4. Or December 2008-January 2006 = 11, but should equal 23.
Use the undocumented DATEDIF function:
=DATEDIF(B1,A1,"m")
(The first date must be the earlier date).
See http://www.cpearson.com/Excel/datedif.aspx for documentation.
--ron