Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How many months between two date range
Hi,
I need a formula that can count how many months between two date range. 11/1/08 6/30/09 = 8 6/5/07 8/25/09 = 27 4/3/08 7/7/09 = 15 Thanks, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How many months between two date range
Use the undocumented DATEDIF function. Look at Chip's detailed explanation
http://www.cpearson.com/Excel/datedif.aspx best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Talladega" wrote in message ... Hi, I need a formula that can count how many months between two date range. 11/1/08 6/30/09 = 8 6/5/07 8/25/09 = 27 4/3/08 7/7/09 = 15 Thanks, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How many months between two date range
I used the formula from the link and it didn't come out right. I have excel
2003. A13 = 1/6/2006 =DATEDIF(6/30/2009,A13,"m") It came out to be 1272. It should be 42 months. "Bernard Liengme" wrote: Use the undocumented DATEDIF function. Look at Chip's detailed explanation http://www.cpearson.com/Excel/datedif.aspx best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Talladega" wrote in message ... Hi, I need a formula that can count how many months between two date range. 11/1/08 6/30/09 = 8 6/5/07 8/25/09 = 27 4/3/08 7/7/09 = 15 Thanks, |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How many months between two date range
Hi,
I think it should be 41, try this =DATEDIF(A13,DATE(2009,6,30),"m") Mike "Talladega" wrote: I used the formula from the link and it didn't come out right. I have excel 2003. A13 = 1/6/2006 =DATEDIF(6/30/2009,A13,"m") It came out to be 1272. It should be 42 months. "Bernard Liengme" wrote: Use the undocumented DATEDIF function. Look at Chip's detailed explanation http://www.cpearson.com/Excel/datedif.aspx best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Talladega" wrote in message ... Hi, I need a formula that can count how many months between two date range. 11/1/08 6/30/09 = 8 6/5/07 8/25/09 = 27 4/3/08 7/7/09 = 15 Thanks, |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How many months between two date range
On the contrary, it came out right.
You started counting from a time tag of 6 divided by 30 then divided by 2009. That is a tiny fraction of a day from Excel's time origin of 1 Jan 1900. From that date until 6 Jan 2006 is 1272 months (106 years). If you are trying to count *FROM* 6 Jan 2006 *TO* 30 Sep 2009, then try =DATEDIF(A13,DATE(2009,9,30),"m") That gives 44 months. Perhaps you can explain how you get 42 months? -- David Biddulph "Talladega" wrote in message ... I used the formula from the link and it didn't come out right. I have excel 2003. A13 = 1/6/2006 =DATEDIF(6/30/2009,A13,"m") It came out to be 1272. It should be 42 months. "Bernard Liengme" wrote: Use the undocumented DATEDIF function. Look at Chip's detailed explanation http://www.cpearson.com/Excel/datedif.aspx best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Talladega" wrote in message ... Hi, I need a formula that can count how many months between two date range. 11/1/08 6/30/09 = 8 6/5/07 8/25/09 = 27 4/3/08 7/7/09 = 15 Thanks, |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How many months between two date range
Let me see, i just counted with my fingers. :)
1/6/2006 thru 12/2006 is 12 months 1/2007 thru 12/2007 is 12 months 1/2008 thru 12/2008 is 12 months 1/2009 thru 6/30/09 is 6 months all the months add up to 42. "David Biddulph" wrote: On the contrary, it came out right. You started counting from a time tag of 6 divided by 30 then divided by 2009. That is a tiny fraction of a day from Excel's time origin of 1 Jan 1900. From that date until 6 Jan 2006 is 1272 months (106 years). If you are trying to count *FROM* 6 Jan 2006 *TO* 30 Sep 2009, then try =DATEDIF(A13,DATE(2009,9,30),"m") That gives 44 months. Perhaps you can explain how you get 42 months? -- David Biddulph "Talladega" wrote in message ... I used the formula from the link and it didn't come out right. I have excel 2003. A13 = 1/6/2006 =DATEDIF(6/30/2009,A13,"m") It came out to be 1272. It should be 42 months. "Bernard Liengme" wrote: Use the undocumented DATEDIF function. Look at Chip's detailed explanation http://www.cpearson.com/Excel/datedif.aspx best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Talladega" wrote in message ... Hi, I need a formula that can count how many months between two date range. 11/1/08 6/30/09 = 8 6/5/07 8/25/09 = 27 4/3/08 7/7/09 = 15 Thanks, |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How many months between two date range
Sarcasm is the lowest form of wit particularly when directed at someone
trying to help!! Get your fingers out again and try this The numbers 1 to 10 (that's all your fingers I don't want to extend you, no toes) 1 to 10 =10 do we agree? similar to your 1/6/2006 thru 12/2006 is 12 months the diference between 1 and 10? Quiet at the back please let me think!! OK 10-1 =9 please sir the diference is 9 and that's how Datedif works Mike "Talladega" wrote: Let me see, i just counted with my fingers. :) 1/6/2006 thru 12/2006 is 12 months 1/2007 thru 12/2007 is 12 months 1/2008 thru 12/2008 is 12 months 1/2009 thru 6/30/09 is 6 months all the months add up to 42. "David Biddulph" wrote: On the contrary, it came out right. You started counting from a time tag of 6 divided by 30 then divided by 2009. That is a tiny fraction of a day from Excel's time origin of 1 Jan 1900. From that date until 6 Jan 2006 is 1272 months (106 years). If you are trying to count *FROM* 6 Jan 2006 *TO* 30 Sep 2009, then try =DATEDIF(A13,DATE(2009,9,30),"m") That gives 44 months. Perhaps you can explain how you get 42 months? -- David Biddulph "Talladega" wrote in message ... I used the formula from the link and it didn't come out right. I have excel 2003. A13 = 1/6/2006 =DATEDIF(6/30/2009,A13,"m") It came out to be 1272. It should be 42 months. "Bernard Liengme" wrote: Use the undocumented DATEDIF function. Look at Chip's detailed explanation http://www.cpearson.com/Excel/datedif.aspx best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Talladega" wrote in message ... Hi, I need a formula that can count how many months between two date range. 11/1/08 6/30/09 = 8 6/5/07 8/25/09 = 27 4/3/08 7/7/09 = 15 Thanks, |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How many months between two date range
Thanks for the clarification and the helpful formula!
I didn't mean to startle anyone. "Mike H" wrote: Sarcasm is the lowest form of wit particularly when directed at someone trying to help!! Get your fingers out again and try this The numbers 1 to 10 (that's all your fingers I don't want to extend you, no toes) 1 to 10 =10 do we agree? similar to your 1/6/2006 thru 12/2006 is 12 months the diference between 1 and 10? Quiet at the back please let me think!! OK 10-1 =9 please sir the diference is 9 and that's how Datedif works Mike "Talladega" wrote: Let me see, i just counted with my fingers. :) 1/6/2006 thru 12/2006 is 12 months 1/2007 thru 12/2007 is 12 months 1/2008 thru 12/2008 is 12 months 1/2009 thru 6/30/09 is 6 months all the months add up to 42. "David Biddulph" wrote: On the contrary, it came out right. You started counting from a time tag of 6 divided by 30 then divided by 2009. That is a tiny fraction of a day from Excel's time origin of 1 Jan 1900. From that date until 6 Jan 2006 is 1272 months (106 years). If you are trying to count *FROM* 6 Jan 2006 *TO* 30 Sep 2009, then try =DATEDIF(A13,DATE(2009,9,30),"m") That gives 44 months. Perhaps you can explain how you get 42 months? -- David Biddulph "Talladega" wrote in message ... I used the formula from the link and it didn't come out right. I have excel 2003. A13 = 1/6/2006 =DATEDIF(6/30/2009,A13,"m") It came out to be 1272. It should be 42 months. "Bernard Liengme" wrote: Use the undocumented DATEDIF function. Look at Chip's detailed explanation http://www.cpearson.com/Excel/datedif.aspx best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Talladega" wrote in message ... Hi, I need a formula that can count how many months between two date range. 11/1/08 6/30/09 = 8 6/5/07 8/25/09 = 27 4/3/08 7/7/09 = 15 Thanks, |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How many months between two date range
Your welcome and if you want to include the start month then simply put +1 on
the end of the datedif formula. Mike "Talladega" wrote: Thanks for the clarification and the helpful formula! I didn't mean to startle anyone. "Mike H" wrote: Sarcasm is the lowest form of wit particularly when directed at someone trying to help!! Get your fingers out again and try this The numbers 1 to 10 (that's all your fingers I don't want to extend you, no toes) 1 to 10 =10 do we agree? similar to your 1/6/2006 thru 12/2006 is 12 months the diference between 1 and 10? Quiet at the back please let me think!! OK 10-1 =9 please sir the diference is 9 and that's how Datedif works Mike "Talladega" wrote: Let me see, i just counted with my fingers. :) 1/6/2006 thru 12/2006 is 12 months 1/2007 thru 12/2007 is 12 months 1/2008 thru 12/2008 is 12 months 1/2009 thru 6/30/09 is 6 months all the months add up to 42. "David Biddulph" wrote: On the contrary, it came out right. You started counting from a time tag of 6 divided by 30 then divided by 2009. That is a tiny fraction of a day from Excel's time origin of 1 Jan 1900. From that date until 6 Jan 2006 is 1272 months (106 years). If you are trying to count *FROM* 6 Jan 2006 *TO* 30 Sep 2009, then try =DATEDIF(A13,DATE(2009,9,30),"m") That gives 44 months. Perhaps you can explain how you get 42 months? -- David Biddulph "Talladega" wrote in message ... I used the formula from the link and it didn't come out right. I have excel 2003. A13 = 1/6/2006 =DATEDIF(6/30/2009,A13,"m") It came out to be 1272. It should be 42 months. "Bernard Liengme" wrote: Use the undocumented DATEDIF function. Look at Chip's detailed explanation http://www.cpearson.com/Excel/datedif.aspx best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Talladega" wrote in message ... Hi, I need a formula that can count how many months between two date range. 11/1/08 6/30/09 = 8 6/5/07 8/25/09 = 27 4/3/08 7/7/09 = 15 Thanks, |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How many months between two date range
"Talladega" wrote:
Let me see, i just counted with my fingers. :) 1/6/2006 thru 12/2006 is 12 months No, it isn't. Consider this: how many months are there from 1/6/2006 to 2/6/2006? The correct answer is: one. So it is 11 months from 1/6/2006 to 12/6/2006. 1/2007 thru 12/2007 is 12 months Well, you skipped a month, namely: 12/6/2006 to 1/6/2007. So the next period is 12/6/2006 to 12/6/2007, which is indeed 12 months. 1/2008 thru 12/2008 is 12 months 1/2009 thru 6/30/09 is 6 months Again, you are skipping months. Those time periods should be 12/6/2007 to 12/6/2008 (12 months) and 12/6/2008 to 6/30/2009 (6 months and 24 days). So the total is 11+12+12+6 = 41 months and 24 days. all the months add up to 42. That depends on how you want to treat additional time (24 days) beyond an integral number of months. That's really up to you. There is no right or wrong way; it all depends on your application (purpose). But you do need to know what the tools (functions) do, and you need to make adjustments if their "judgment" differs from you. Most functions are not mindreaders ;-). DATEDIF truncates. If you want to round up, you might use some heuristic like the following. Suppose 1/6/2006 is in A1 and 6/30/2006 is in B1. Then: =datedif(A1,B1) + (edate(A1,datedif(A1,B1))<B1) If you get a #NAME? error, see the Help page for the EDATE() function. HTH. ----- original posting ----- "Talladega" wrote in message ... Let me see, i just counted with my fingers. :) 1/6/2006 thru 12/2006 is 12 months 1/2007 thru 12/2007 is 12 months 1/2008 thru 12/2008 is 12 months 1/2009 thru 6/30/09 is 6 months all the months add up to 42. "David Biddulph" wrote: On the contrary, it came out right. You started counting from a time tag of 6 divided by 30 then divided by 2009. That is a tiny fraction of a day from Excel's time origin of 1 Jan 1900. From that date until 6 Jan 2006 is 1272 months (106 years). If you are trying to count *FROM* 6 Jan 2006 *TO* 30 Sep 2009, then try =DATEDIF(A13,DATE(2009,9,30),"m") That gives 44 months. Perhaps you can explain how you get 42 months? -- David Biddulph "Talladega" wrote in message ... I used the formula from the link and it didn't come out right. I have excel 2003. A13 = 1/6/2006 =DATEDIF(6/30/2009,A13,"m") It came out to be 1272. It should be 42 months. "Bernard Liengme" wrote: Use the undocumented DATEDIF function. Look at Chip's detailed explanation http://www.cpearson.com/Excel/datedif.aspx best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Talladega" wrote in message ... Hi, I need a formula that can count how many months between two date range. 11/1/08 6/30/09 = 8 6/5/07 8/25/09 = 27 4/3/08 7/7/09 = 15 Thanks, |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How many months between two date range
"Mike H" wrote:
Your welcome and if you want to include the start month then simply put +1 on the end of the datedif formula. I don't think it is that simple. Two cases to consider: 1. =datedif(datevalue("1/10/2008"),datevalue("2/10/2008"),"m") Should always result in 1. If we "simply put +1 on the end", we get 2. 2. =datedif(datevalue("2/28/2007"),datevalue("2/29/2008"),"m") In some contexts, we might reasonably want the result to be 12. If we "simply put +1 on the end", we get 13. FYI, the same can be said for my proposal of adding 1 depending on EDATE(). How about: =roundup(days360(A1,B1)/30,0) ----- original posting ----- "Mike H" wrote in message ... Your welcome and if you want to include the start month then simply put +1 on the end of the datedif formula. Mike "Talladega" wrote: Thanks for the clarification and the helpful formula! I didn't mean to startle anyone. "Mike H" wrote: Sarcasm is the lowest form of wit particularly when directed at someone trying to help!! Get your fingers out again and try this The numbers 1 to 10 (that's all your fingers I don't want to extend you, no toes) 1 to 10 =10 do we agree? similar to your 1/6/2006 thru 12/2006 is 12 months the diference between 1 and 10? Quiet at the back please let me think!! OK 10-1 =9 please sir the diference is 9 and that's how Datedif works Mike "Talladega" wrote: Let me see, i just counted with my fingers. :) 1/6/2006 thru 12/2006 is 12 months 1/2007 thru 12/2007 is 12 months 1/2008 thru 12/2008 is 12 months 1/2009 thru 6/30/09 is 6 months all the months add up to 42. "David Biddulph" wrote: On the contrary, it came out right. You started counting from a time tag of 6 divided by 30 then divided by 2009. That is a tiny fraction of a day from Excel's time origin of 1 Jan 1900. From that date until 6 Jan 2006 is 1272 months (106 years). If you are trying to count *FROM* 6 Jan 2006 *TO* 30 Sep 2009, then try =DATEDIF(A13,DATE(2009,9,30),"m") That gives 44 months. Perhaps you can explain how you get 42 months? -- David Biddulph "Talladega" wrote in message ... I used the formula from the link and it didn't come out right. I have excel 2003. A13 = 1/6/2006 =DATEDIF(6/30/2009,A13,"m") It came out to be 1272. It should be 42 months. "Bernard Liengme" wrote: Use the undocumented DATEDIF function. Look at Chip's detailed explanation http://www.cpearson.com/Excel/datedif.aspx best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Talladega" wrote in message ... Hi, I need a formula that can count how many months between two date range. 11/1/08 6/30/09 = 8 6/5/07 8/25/09 = 27 4/3/08 7/7/09 = 15 Thanks, |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How many months between two date range
Try this
=INT((A2-A1)/30) Where A1 and A2 are end and start date. -- HARSHAWARDHAN.S.SHASTRI Pl do not forget to press "YES" button if post found useful. "Talladega" wrote: Hi, I need a formula that can count how many months between two date range. 11/1/08 6/30/09 = 8 6/5/07 8/25/09 = 27 4/3/08 7/7/09 = 15 Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3 months prior and 3 months post a date | Excel Worksheet Functions | |||
About calculate months between two date (date function) | Excel Discussion (Misc queries) | |||
Range Changes with the Months | Excel Worksheet Functions | |||
Convert date to length of time in months from set date | Excel Worksheet Functions | |||
Return a date 6 months from a date in another cell | Excel Worksheet Functions |