date calculation

[Pammy]

If I have 2 date columns how do I create a formula to subtrack? example:
(b4) November 30, 2007 and (c4) November 6, 1986. My formula is:
=month(b4)-month(c4) in column D, but I get 0 and I have formatted the
column to numbers.
I also did =month(c4)-month(b4). Is there another way to this to get the
correct #?

 

[JE McGimpsey]

And what *is* the correct number? Do you want 'whole' months? (months


=DATEDIF(C4,B4,"m")

(see http://cpearson.com/excel/datedif.htm for documentation)

 

[Ron Rosenfeld]

If I have 2 date columns how do I create a formula to subtrack? example:
(b4) November 30, 2007 and (c4) November 6, 1986. My formula is:
=month(b4)-month(c4) in column D, but I get 0 and I have formatted the
column to numbers.
I also did =month(c4)-month(b4). Is there another way to this to get the
correct #?

Here's why you get zero:

MONTH worksheet function: Returns the month of a date represented by a serial
number. The month is given as an integer, ranging from 1 (January) to 12
(December).

B4: November 30, 2007

=MONTH(B4) --> 11 (November is the eleventh month)

C4: November 6, 1986

=MONTH(C4) --> 11 (November is the eleventh month).

11-11 = 0

You can use the undocumented DATEDIF function

=DATEDIF(C4,B4,"m")

For documentation, see http://www.cpearson.com/excel/datedif.htm


--ron

 

[Bernie Deitrick]

Pammy,

=DATEDIF(C4,B4,"m")

In general,

=DATEDIF(older date, newer date,"m")

See

http://www.cpearson.com/excel/datedif.htm

 

[SpreadsheetBrian]

Pammy,

=DATEDIF(C4,B4,"m")

In general,

=DATEDIF(older date, newer date,"m")

See

http://www.cpearson.com/excel/datedif.htm

Your formula " =month(b4)-month(c4)" results in 11 - 11 because both
dates occur in the 11th month; November.

What you may want is just "=b4-c4" which will give you a floating
point number of days, specifically 7,694 days. You can divide this
number by 365.25 [e.g. "=(b4-c4)/365.25"] to get a close
approximation of how many years are between the two dates.


Brian Herbert Withun