Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
date calculation
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 #? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
date calculation
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) In article , Pammy wrote: 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 #? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
date calculation
On Tue, 12 Jun 2007 08:46:01 -0700, Pammy
wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
date calculation
Pammy,
=DATEDIF(C4,B4,"m") In general, =DATEDIF(older date, newer date,"m") See http://www.cpearson.com/excel/datedif.htm -- HTH, Bernie MS Excel MVP "Pammy" wrote in message ... 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 #? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
date calculation
On Jun 12, 1:24 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote: Pammy, =DATEDIF(C4,B4,"m") In general, =DATEDIF(older date, newer date,"m") See http://www.cpearson.com/excel/datedif.htm -- HTH, Bernie MS Excel MVP "Pammy" wrote in message ... 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 #? 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tricky Date calculation: How to calculate a future date | Excel Discussion (Misc queries) | |||
Date calculation | Excel Worksheet Functions | |||
Date Calculation | Excel Discussion (Misc queries) | |||
Date calculation | Excel Worksheet Functions | |||
date calculation | Excel Worksheet Functions |