If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
How do I create formula to calc difference in dates?
I have a 'hire date' & a 'term date'. I need to calcuate the difference in
months & don't want to do the calculations manually. EX. 7/1/06 1/1/06 = 6 months |
#2
|
|||
|
|||
How do I create formula to calc difference in dates?
dlcroswell,
If 7/1/06 is in cell a1, and 1/1/06 is in cell b2 then in cell c1* =MONTH(A1)-MONTH(B1) *note, you may have to format cells to general or number Hope this helps ---Bigpig--- "dlcroswell" wrote: I have a 'hire date' & a 'term date'. I need to calcuate the difference in months & don't want to do the calculations manually. EX. 7/1/06 1/1/06 = 6 months |
#3
|
|||
|
|||
How do I create formula to calc difference in dates?
=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. |
#4
|
|||
|
|||
How do I create formula to calc difference in dates?
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 |
#5
|
|||
|
|||
How do I create formula to calc difference in dates?
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 |
#6
|
|||
|
|||
How do I create formula to calc difference in dates?
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 |
#7
|
|||
|
|||
How do I create formula to calc difference in dates?
I have tried this function but am having trouble with it returning the right
value. I have an end-date of 1/20/2010 (in cell F16) and a start-date of 2/11/2009 (in cell E16). I'm trying to calculate how many automatic monthly transfers there will be on the 20th of each month but the function is returning a value of 11 when it should return a value of 12. The first transfer will be 2/20/2009 and the last transfer will be 1/20/2010…a total of 12 transfers. "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 |
#8
|
|||
|
|||
How do I create formula to calc difference in dates?
"dlcroswell" wrote: I have a 'hire date' & a 'term date'. I need to calcuate the difference in months & don't want to do the calculations manually. EX. 7/1/06 1/1/06 = 6 months |
Thread Tools | |
Display Modes | |
|
|