|
Calculate months and years
I want to be able to enter two dates and have the computer provide the number
of months and the number of years to two decimal places. It ought to be easy but I cannot figure this out. I have used the YEARFRAC formula and that sort of works... Except if I put in two dates that are a year apart (July 1, 2004 and June 30, 2005 for example) I do not get 1 year or 12 months. I get fractions in both instances. Same with July 1 and July 31. That is one month but I get a fraction. Even if excel did not recognize that July 1 and July 31 represents exactly 1 month and instead, I had to put August 1 in, I could live with this because I could just modify the formula but even that does not work. The Dateif command does not work as it only provides complete months/years and I need fractions. Any ideas would be greatly appreciated. Ned. |
Hi
and how do you want to calculate the fractions. Always based on 30 days per month? -- Regards Frank Kabel Frankfurt, Germany "Ned Ludd" schrieb im Newsbeitrag ... I want to be able to enter two dates and have the computer provide the number of months and the number of years to two decimal places. It ought to be easy but I cannot figure this out. I have used the YEARFRAC formula and that sort of works... Except if I put in two dates that are a year apart (July 1, 2004 and June 30, 2005 for example) I do not get 1 year or 12 months. I get fractions in both instances. Same with July 1 and July 31. That is one month but I get a fraction. Even if excel did not recognize that July 1 and July 31 represents exactly 1 month and instead, I had to put August 1 in, I could live with this because I could just modify the formula but even that does not work. The Dateif command does not work as it only provides complete months/years and I need fractions. Any ideas would be greatly appreciated. Ned. |
No. Unfortunately, I would need to calculate fractions based on the days in
the partial month. So, if I had a period that ran from July 1 to August 15th then I would need the computer to calculate this as 1.48 months. July 1 - July 31 = 1 month August 1 - August 15 = .48 months (ie 15/31 days in August) I do not know if this can be done. I do appreciate your interest though Frank and if you can help, I'd appreciate it. Ned. "Frank Kabel" wrote: Hi and how do you want to calculate the fractions. Always based on 30 days per month? -- Regards Frank Kabel Frankfurt, Germany "Ned Ludd" schrieb im Newsbeitrag ... I want to be able to enter two dates and have the computer provide the number of months and the number of years to two decimal places. It ought to be easy but I cannot figure this out. I have used the YEARFRAC formula and that sort of works... Except if I put in two dates that are a year apart (July 1, 2004 and June 30, 2005 for example) I do not get 1 year or 12 months. I get fractions in both instances. Same with July 1 and July 31. That is one month but I get a fraction. Even if excel did not recognize that July 1 and July 31 represents exactly 1 month and instead, I had to put August 1 in, I could live with this because I could just modify the formula but even that does not work. The Dateif command does not work as it only provides complete months/years and I need fractions. Any ideas would be greatly appreciated. Ned. |
Hi
not fully tested but try: =DATEDIF(A1,B1,"m")-(DAY(A1)-1)/DAY(DATE(YEAR(A1),MONTH(A1)+1,0))+DAY(B 1)/DAY(DATE(YEAR(B1),MONTH(B1)+1,0)) with A1: starting date B1: ending date Could probably be shortened :-) -- Regards Frank Kabel Frankfurt, Germany "Ned Ludd" schrieb im Newsbeitrag ... No. Unfortunately, I would need to calculate fractions based on the days in the partial month. So, if I had a period that ran from July 1 to August 15th then I would need the computer to calculate this as 1.48 months. July 1 - July 31 = 1 month August 1 - August 15 = .48 months (ie 15/31 days in August) I do not know if this can be done. I do appreciate your interest though Frank and if you can help, I'd appreciate it. Ned. "Frank Kabel" wrote: Hi and how do you want to calculate the fractions. Always based on 30 days per month? -- Regards Frank Kabel Frankfurt, Germany "Ned Ludd" schrieb im Newsbeitrag ... I want to be able to enter two dates and have the computer provide the number of months and the number of years to two decimal places. It ought to be easy but I cannot figure this out. I have used the YEARFRAC formula and that sort of works... Except if I put in two dates that are a year apart (July 1, 2004 and June 30, 2005 for example) I do not get 1 year or 12 months. I get fractions in both instances. Same with July 1 and July 31. That is one month but I get a fraction. Even if excel did not recognize that July 1 and July 31 represents exactly 1 month and instead, I had to put August 1 in, I could live with this because I could just modify the formula but even that does not work. The Dateif command does not work as it only provides complete months/years and I need fractions. Any ideas would be greatly appreciated. Ned. |
Frank. That is awesome. It works perfectly.
Thank you very much! Ned. "Frank Kabel" wrote: Hi not fully tested but try: =DATEDIF(A1,B1,"m")-(DAY(A1)-1)/DAY(DATE(YEAR(A1),MONTH(A1)+1,0))+DAY(B 1)/DAY(DATE(YEAR(B1),MONTH(B1)+1,0)) with A1: starting date B1: ending date Could probably be shortened :-) -- Regards Frank Kabel Frankfurt, Germany "Ned Ludd" schrieb im Newsbeitrag ... No. Unfortunately, I would need to calculate fractions based on the days in the partial month. So, if I had a period that ran from July 1 to August 15th then I would need the computer to calculate this as 1.48 months. July 1 - July 31 = 1 month August 1 - August 15 = .48 months (ie 15/31 days in August) I do not know if this can be done. I do appreciate your interest though Frank and if you can help, I'd appreciate it. Ned. "Frank Kabel" wrote: Hi and how do you want to calculate the fractions. Always based on 30 days per month? -- Regards Frank Kabel Frankfurt, Germany "Ned Ludd" schrieb im Newsbeitrag ... I want to be able to enter two dates and have the computer provide the number of months and the number of years to two decimal places. It ought to be easy but I cannot figure this out. I have used the YEARFRAC formula and that sort of works... Except if I put in two dates that are a year apart (July 1, 2004 and June 30, 2005 for example) I do not get 1 year or 12 months. I get fractions in both instances. Same with July 1 and July 31. That is one month but I get a fraction. Even if excel did not recognize that July 1 and July 31 represents exactly 1 month and instead, I had to put August 1 in, I could live with this because I could just modify the formula but even that does not work. The Dateif command does not work as it only provides complete months/years and I need fractions. Any ideas would be greatly appreciated. Ned. |
Hi
thanks for your reply. Still think the formula could be simplified :-) -- Regards Frank Kabel Frankfurt, Germany Ned Ludd wrote: Frank. That is awesome. It works perfectly. Thank you very much! Ned. "Frank Kabel" wrote: Hi not fully tested but try: =DATEDIF(A1,B1,"m")-(DAY(A1)-1)/DAY(DATE(YEAR(A1),MONTH(A1)+1,0))+DAY(B 1)/DAY(DATE(YEAR(B1),MONTH(B1)+1,0)) with A1: starting date B1: ending date Could probably be shortened :-) -- Regards Frank Kabel Frankfurt, Germany "Ned Ludd" schrieb im Newsbeitrag ... No. Unfortunately, I would need to calculate fractions based on the days in the partial month. So, if I had a period that ran from July 1 to August 15th then I would need the computer to calculate this as 1.48 months. July 1 - July 31 = 1 month August 1 - August 15 = .48 months (ie 15/31 days in August) I do not know if this can be done. I do appreciate your interest though Frank and if you can help, I'd appreciate it. Ned. "Frank Kabel" wrote: Hi and how do you want to calculate the fractions. Always based on 30 days per month? -- Regards Frank Kabel Frankfurt, Germany "Ned Ludd" schrieb im Newsbeitrag ... I want to be able to enter two dates and have the computer provide the number of months and the number of years to two decimal places. It ought to be easy but I cannot figure this out. I have used the YEARFRAC formula and that sort of works... Except if I put in two dates that are a year apart (July 1, 2004 and June 30, 2005 for example) I do not get 1 year or 12 months. I get fractions in both instances. Same with July 1 and July 31. That is one month but I get a fraction. Even if excel did not recognize that July 1 and July 31 represents exactly 1 month and instead, I had to put August 1 in, I could live with this because I could just modify the formula but even that does not work. The Dateif command does not work as it only provides complete months/years and I need fractions. Any ideas would be greatly appreciated. Ned. |
| All times are GMT +1. The time now is 10:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com