Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create formula to calc difference in dates?
Try this:
E16 = start date F16 = end date =SUMPRODUCT(--(DAY(ROW(INDIRECT(E16&":"&F16)))=20)) That will count how many 20th of the months there are from a start date to an end date (inclusive). -- Biff Microsoft Excel MVP "VSlaybaugh" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create formula to calc difference in dates?
Very impressive, Biff.
Fred "T. Valko" wrote in message ... Try this: E16 = start date F16 = end date =SUMPRODUCT(--(DAY(ROW(INDIRECT(E16&":"&F16)))=20)) That will count how many 20th of the months there are from a start date to an end date (inclusive). -- Biff Microsoft Excel MVP "VSlaybaugh" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create formula to calc difference in dates?
Thanks!
-- Biff Microsoft Excel MVP "Fred Smith" wrote in message ... Very impressive, Biff. Fred "T. Valko" wrote in message ... Try this: E16 = start date F16 = end date =SUMPRODUCT(--(DAY(ROW(INDIRECT(E16&":"&F16)))=20)) That will count how many 20th of the months there are from a start date to an end date (inclusive). -- Biff Microsoft Excel MVP "VSlaybaugh" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create formula to calc difference in dates?
Wow! This works great. I appreciate your quick responses when I submit a
problem I'm having. You always have a solution that works. I just wish I understood what you did to make it happen. :-) "T. Valko" wrote: Try this: E16 = start date F16 = end date =SUMPRODUCT(--(DAY(ROW(INDIRECT(E16&":"&F16)))=20)) That will count how many 20th of the months there are from a start date to an end date (inclusive). -- Biff Microsoft Excel MVP "VSlaybaugh" wrote in message ... 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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create formula to calc difference in dates?
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "VSlaybaugh" wrote in message ... Wow! This works great. I appreciate your quick responses when I submit a problem I'm having. You always have a solution that works. I just wish I understood what you did to make it happen. :-) "T. Valko" wrote: Try this: E16 = start date F16 = end date =SUMPRODUCT(--(DAY(ROW(INDIRECT(E16&":"&F16)))=20)) That will count how many 20th of the months there are from a start date to an end date (inclusive). -- Biff Microsoft Excel MVP "VSlaybaugh" wrote in message ... 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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create formula to calc difference in dates?
Can you help me in finding difference of data between 12/10/2003 &
2/14/2009. With datedif or usual cell1 - cell2 formulae it shows 1893 days where as my manual calculation shows it to be Year Days 2003 52 2004 360 2005 360 2006 360 2007 360 2008 360 2009 45 Total 1897 Appreciate you help. Imran "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "VSlaybaugh" wrote in message ... Wow! This works great. I appreciate your quick responses when I submit a problem I'm having. You always have a solution that works. I just wish I understood what you did to make it happen. :-) "T. Valko" wrote: Try this: E16 = start date F16 = end date =SUMPRODUCT(--(DAY(ROW(INDIRECT(E16&":"&F16)))=20)) That will count how many 20th of the months there are from a start date to an end date (inclusive). -- Biff Microsoft Excel MVP "VSlaybaugh" wrote in message ... 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 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create formula to calc difference in dates?
sorry but the actual manual calculation is as follows:
Year Days 2003 52 2004 364 2005 364 2006 365 2007 364 2008 364 2009 45 Total days 1918 "Imran ul Haque" wrote: Can you help me in finding difference of data between 12/10/2003 & 2/14/2009. With datedif or usual cell1 - cell2 formulae it shows 1893 days where as my manual calculation shows it to be Year Days 2003 52 2004 360 2005 360 2006 360 2007 360 2008 360 2009 45 Total 1897 Appreciate you help. Imran "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "VSlaybaugh" wrote in message ... Wow! This works great. I appreciate your quick responses when I submit a problem I'm having. You always have a solution that works. I just wish I understood what you did to make it happen. :-) "T. Valko" wrote: Try this: E16 = start date F16 = end date =SUMPRODUCT(--(DAY(ROW(INDIRECT(E16&":"&F16)))=20)) That will count how many 20th of the months there are from a start date to an end date (inclusive). -- Biff Microsoft Excel MVP "VSlaybaugh" wrote in message ... 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 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create formula to calc difference in dates?
Imran,
First, if you have a new query, start a new thread. When you add to an existing thread, there's too much chance it will get lost. Second, there are 365 days in a year, not 364. In leap years, there are 366. Third, you need to be consistent in your date formats. Because the second date can only be Feb 14/09, the first date must be Dec 10/03, not Oct 12. So, your actual days in between a 2003: 21 2004: 366 2005: 365 2006: 365 2007: 365 2008: 366 2009: 45 Total: 1893 Regards, Fred. "Imran ul Haque" wrote in message ... sorry but the actual manual calculation is as follows: Year Days 2003 52 2004 364 2005 364 2006 365 2007 364 2008 364 2009 45 Total days 1918 "Imran ul Haque" wrote: Can you help me in finding difference of data between 12/10/2003 & 2/14/2009. With datedif or usual cell1 - cell2 formulae it shows 1893 days where as my manual calculation shows it to be Year Days 2003 52 2004 360 2005 360 2006 360 2007 360 2008 360 2009 45 Total 1897 Appreciate you help. Imran "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "VSlaybaugh" wrote in message ... Wow! This works great. I appreciate your quick responses when I submit a problem I'm having. You always have a solution that works. I just wish I understood what you did to make it happen. :-) "T. Valko" wrote: Try this: E16 = start date F16 = end date =SUMPRODUCT(--(DAY(ROW(INDIRECT(E16&":"&F16)))=20)) That will count how many 20th of the months there are from a start date to an end date (inclusive). -- Biff Microsoft Excel MVP "VSlaybaugh" wrote in message ... 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 |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create formula to calc difference in dates?
Hi Biff
If I wanted to use a formual to count days including the start date and end dates what would i use? e.g days between january 1, 2009 - january 7, 2009 is seven days, but using the formulas it gives a result of 6 instead of seven Thanks Ken |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create formula to calc difference in dates?
To me it is only 6 days, how do you get 7?
-- Regards, Peo Sjoblom "Kenneth Green" <Kenneth wrote in message ... Hi Biff If I wanted to use a formual to count days including the start date and end dates what would i use? e.g days between january 1, 2009 - january 7, 2009 is seven days, but using the formulas it gives a result of 6 instead of seven Thanks Ken |
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create formula to calc difference in dates?
You could just add one more to the result:
=a1-a2+1 Kenneth Green wrote: Hi Biff If I wanted to use a formual to count days including the start date and end dates what would i use? e.g days between january 1, 2009 - january 7, 2009 is seven days, but using the formulas it gives a result of 6 instead of seven Thanks Ken -- Dave Peterson |
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create formula to calc difference in dates?
Subtracting one date from another is always going to give that result.
It's the same as subtracting one number from another. If you want inclusive, you will have to add one back or subtract the day before the start day. Kenneth Green wrote: Hi Biff If I wanted to use a formual to count days including the start date and end dates what would i use? e.g days between january 1, 2009 - january 7, 2009 is seven days, but using the formulas it gives a result of 6 instead of seven Thanks Ken |
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create formula to calc difference in dates?
Hi I'm hoping someone can point me in the right direction. I tried the
formula listed here and a few others and I can seem to get the right result. I need a column to calculate age. I have todays date in G17 and the DOB in G3. "T. Valko" wrote: Try this: E16 = start date F16 = end date =SUMPRODUCT(--(DAY(ROW(INDIRECT(E16&":"&F16)))=20)) That will count how many 20th of the months there are from a start date to an end date (inclusive). -- Biff Microsoft Excel MVP "VSlaybaugh" wrote in message ... 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 |
#22
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create formula to calc difference in dates?
=DATEDIF(G3,G17,"y") & "years," & DATEDIF(G3,G17,"ym") & "months,"&
DATEDIF(G3,G17,"md") & "days," For more help on the undocumented DATEDIF function see Chip Pearson's site. http://www.cpearson.com/excel/datedif.aspx Gord Dibben MS Excel MVP On Wed, 12 May 2010 13:43:01 -0700, C wrote: Hi I'm hoping someone can point me in the right direction. I tried the formula listed here and a few others and I can seem to get the right result. I need a column to calculate age. I have todays date in G17 and the DOB in G3. "T. Valko" wrote: Try this: E16 = start date F16 = end date =SUMPRODUCT(--(DAY(ROW(INDIRECT(E16&":"&F16)))=20)) That will count how many 20th of the months there are from a start date to an end date (inclusive). -- Biff Microsoft Excel MVP "VSlaybaugh" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
what is the formula to show the difference between two dates | Excel Discussion (Misc queries) | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula for calc diff between two julian dates | Excel Discussion (Misc queries) | |||
how do i put formula to find difference between two dates | New Users to Excel |