How calculate exact # of months between two dates?
Hi,
Right now I am using the DATEDIF(CELL1, CELL2, "m") function. However, it provides only single digit response. So, if the two dates are 20 JUNE and 20 SEPTEMBER, it will be 2 months. However, if the two dates are 20 JUNE and 20 SEPTEMBER, it give me 1 month instead of 1 month and X days (or, 1.x << this is exactly output that I am trying to achieve) Any idea how to do that? Excel 2003. Thanks! |
How calculate exact # of months between two dates?
Hi,
=DATEDIF(D5,E5,"y")&" years "&DATEDIF(D5,E5,"ym")&" months "&DATEDIF(D5,E5,"md")&" days" Change range to fit your needs if this helps please click yes, thanks "Mark Livingstone" wrote: Hi, Right now I am using the DATEDIF(CELL1, CELL2, "m") function. However, it provides only single digit response. So, if the two dates are 20 JUNE and 20 SEPTEMBER, it will be 2 months. However, if the two dates are 20 JUNE and 20 SEPTEMBER, it give me 1 month instead of 1 month and X days (or, 1.x << this is exactly output that I am trying to achieve) Any idea how to do that? Excel 2003. Thanks! |
How calculate exact # of months between two dates?
Hi,
Just a personal opinion but month is just about the daftest interval for measuring time there is, is a month 28,29,30 or 31 days Hmm. Anyway, I think this is what you want =DATEDIF(A1,B1,"m")&"."&DATEDIF(A1,B1,"md") For help look here http://www.cpearson.com/excel/datedif.htm Mike "Mark Livingstone" wrote: Hi, Right now I am using the DATEDIF(CELL1, CELL2, "m") function. However, it provides only single digit response. So, if the two dates are 20 JUNE and 20 SEPTEMBER, it will be 2 months. However, if the two dates are 20 JUNE and 20 SEPTEMBER, it give me 1 month instead of 1 month and X days (or, 1.x << this is exactly output that I am trying to achieve) Any idea how to do that? Excel 2003. Thanks! |
How calculate exact # of months between two dates?
Eduardo,
test your formula with these 2 dates 31/01/1943 01/03/2008 There are countless combinations that throw up this anomaly Mike "Eduardo" wrote: Hi, =DATEDIF(D5,E5,"y")&" years "&DATEDIF(D5,E5,"ym")&" months "&DATEDIF(D5,E5,"md")&" days" Change range to fit your needs if this helps please click yes, thanks "Mark Livingstone" wrote: Hi, Right now I am using the DATEDIF(CELL1, CELL2, "m") function. However, it provides only single digit response. So, if the two dates are 20 JUNE and 20 SEPTEMBER, it will be 2 months. However, if the two dates are 20 JUNE and 20 SEPTEMBER, it give me 1 month instead of 1 month and X days (or, 1.x << this is exactly output that I am trying to achieve) Any idea how to do that? Excel 2003. Thanks! |
How calculate exact # of months between two dates?
Thank you Eduardo and Mike H.
Mike H's formula worked. This is exactly what I was looking for :) |
How calculate exact # of months between two dates?
Hi Mike,
I check the formula and give me 65 years, 1month and 1 day, if I apply the formula given by Cpearson in the link you sent it give me the same result, I am intereste to know where the difference is, sorry if it's obvious but I don't see it "Mike H" wrote: Eduardo, test your formula with these 2 dates 31/01/1943 01/03/2008 There are countless combinations that throw up this anomaly Mike "Eduardo" wrote: Hi, =DATEDIF(D5,E5,"y")&" years "&DATEDIF(D5,E5,"ym")&" months "&DATEDIF(D5,E5,"md")&" days" Change range to fit your needs if this helps please click yes, thanks "Mark Livingstone" wrote: Hi, Right now I am using the DATEDIF(CELL1, CELL2, "m") function. However, it provides only single digit response. So, if the two dates are 20 JUNE and 20 SEPTEMBER, it will be 2 months. However, if the two dates are 20 JUNE and 20 SEPTEMBER, it give me 1 month instead of 1 month and X days (or, 1.x << this is exactly output that I am trying to achieve) Any idea how to do that? Excel 2003. Thanks! |
How calculate exact # of months between two dates?
On Jul 21, 2:45*pm, Mike H wrote:
Hi, Just a personal opinion but month is just about the daftest interval for measuring time there is, is a month 28,29,30 or 31 days Hmm. Anyway, I think this is what you want =DATEDIF(A1,B1,"m")&"."&DATEDIF(A1,B1,"md") The difference between 7/22/2009 and 8/21/2009 appears to be 0.30. Odd. |
How calculate exact # of months between two dates?
Eduardo,
Your formula returns 65 years 1 months -1 days for those 2 dates. The second date is 1 March 2008. It is a well documented error in datedif and I have a pet theory (probably incorrect) that this is the reason it isn't documented. Mike "Eduardo" wrote: Hi Mike, I check the formula and give me 65 years, 1month and 1 day, if I apply the formula given by Cpearson in the link you sent it give me the same result, I am intereste to know where the difference is, sorry if it's obvious but I don't see it "Mike H" wrote: Eduardo, test your formula with these 2 dates 31/01/1943 01/03/2008 There are countless combinations that throw up this anomaly Mike "Eduardo" wrote: Hi, =DATEDIF(D5,E5,"y")&" years "&DATEDIF(D5,E5,"ym")&" months "&DATEDIF(D5,E5,"md")&" days" Change range to fit your needs if this helps please click yes, thanks "Mark Livingstone" wrote: Hi, Right now I am using the DATEDIF(CELL1, CELL2, "m") function. However, it provides only single digit response. So, if the two dates are 20 JUNE and 20 SEPTEMBER, it will be 2 months. However, if the two dates are 20 JUNE and 20 SEPTEMBER, it give me 1 month instead of 1 month and X days (or, 1.x << this is exactly output that I am trying to achieve) Any idea how to do that? Excel 2003. Thanks! |
How calculate exact # of months between two dates?
Eduardo,
It's probably clearer if you just use the "md" part of your formula =DATEDIF(D5,E5,"md")&" days" and note it returns (minus) -1 days Mike "Mike H" wrote: Eduardo, Your formula returns 65 years 1 months -1 days for those 2 dates. The second date is 1 March 2008. It is a well documented error in datedif and I have a pet theory (probably incorrect) that this is the reason it isn't documented. Mike "Eduardo" wrote: Hi Mike, I check the formula and give me 65 years, 1month and 1 day, if I apply the formula given by Cpearson in the link you sent it give me the same result, I am intereste to know where the difference is, sorry if it's obvious but I don't see it "Mike H" wrote: Eduardo, test your formula with these 2 dates 31/01/1943 01/03/2008 There are countless combinations that throw up this anomaly Mike "Eduardo" wrote: Hi, =DATEDIF(D5,E5,"y")&" years "&DATEDIF(D5,E5,"ym")&" months "&DATEDIF(D5,E5,"md")&" days" Change range to fit your needs if this helps please click yes, thanks "Mark Livingstone" wrote: Hi, Right now I am using the DATEDIF(CELL1, CELL2, "m") function. However, it provides only single digit response. So, if the two dates are 20 JUNE and 20 SEPTEMBER, it will be 2 months. However, if the two dates are 20 JUNE and 20 SEPTEMBER, it give me 1 month instead of 1 month and X days (or, 1.x << this is exactly output that I am trying to achieve) Any idea how to do that? Excel 2003. Thanks! |
How calculate exact # of months between two dates?
Hi,
Is there a date range over which this anomaly occurs I.e. what does an end user need to know (do's and dont's) before using the DATEDIF() function. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mike H" wrote in message ... Eduardo, Your formula returns 65 years 1 months -1 days for those 2 dates. The second date is 1 March 2008. It is a well documented error in datedif and I have a pet theory (probably incorrect) that this is the reason it isn't documented. Mike "Eduardo" wrote: Hi Mike, I check the formula and give me 65 years, 1month and 1 day, if I apply the formula given by Cpearson in the link you sent it give me the same result, I am intereste to know where the difference is, sorry if it's obvious but I don't see it "Mike H" wrote: Eduardo, test your formula with these 2 dates 31/01/1943 01/03/2008 There are countless combinations that throw up this anomaly Mike "Eduardo" wrote: Hi, =DATEDIF(D5,E5,"y")&" years "&DATEDIF(D5,E5,"ym")&" months "&DATEDIF(D5,E5,"md")&" days" Change range to fit your needs if this helps please click yes, thanks "Mark Livingstone" wrote: Hi, Right now I am using the DATEDIF(CELL1, CELL2, "m") function. However, it provides only single digit response. So, if the two dates are 20 JUNE and 20 SEPTEMBER, it will be 2 months. However, if the two dates are 20 JUNE and 20 SEPTEMBER, it give me 1 month instead of 1 month and X days (or, 1.x << this is exactly output that I am trying to achieve) Any idea how to do that? Excel 2003. Thanks! |
All times are GMT +1. The time now is 03:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com