Calculation of monthly duration
Hi All,
I've been trying to find a formula to automatically calculate duration in months from start date and end date. I went through several threads here but none of them seems to fit at all. Could any of you advise? I presume it is something in terms of =[End Date]@row - [Start Date]@row, unfortunately this shows me duration in days, not in months. any advice please?
Best Answer
-
I didn't even notice that bit. The error isn't actually on the number of years. For example...
1 Jan 20 - 31 Dec 20 is currently showing as 13 months, but if you look at 1 Jan 20 - 31 Dec 25 it is showing as 73 instead of adding an additional month per year. I know what the problem is, but I am not sure if I can explain it properly. The solution though is to simply subtract 1.
=(12 - MONTH(Start@row) + (((YEAR(Finish@row) - YEAR(Start@row)) * 12) - 12) + MONTH(Finish@row)) + ((((IFERROR(DATE(YEAR(Start@row), MONTH(Start@row) + 1, 1), DATE(YEAR(Start@row) + 1, 1, 1)) - 1) - Start@row) + 1) / DAY(IFERROR(DATE(YEAR(Start@row), MONTH(Start@row) + 1, 1), DATE(YEAR(Start@row) + 1, 1, 1)) - 1)) + (DAY(Finish@row) / DAY(IFERROR(DATE(YEAR(Finish@row), MONTH(Finish@row) + 1, 1), DATE(YEAR(Finish@row) + 1, 1, 1)) - 1)) - 1
Answers
-
Give something like this a try...
=12 - MONTH(Start@row) + (((YEAR(Finish@row) - YEAR(Start@row)) * 12) - 12) + MONTH(Finish@row)
-
@Paul Newcome thanks Paul. While this works fine, is there a way it would show more precisely? e.g 3.89 months instead of 4? as in, if an activity started 15-jan-2020 and ended 31-dec-2020, it should be 11.5 months. Do you know where I am coming from?
-
To do that we need to figure out how many days are in the month. To do this we start by finding the first of the next month and subtracting a day.
=IFERROR(DATE(YEAR(Start@row), MONTH(Start@row) + 1, 1), DATE(YEAR(Start@row) + 1, 1, 1) - 1
=IFERROR(DATE(YEAR(Finish@row), MONTH(Finish@row) + 1, 1), DATE(YEAR(Finish@row) + 1, 1, 1) - 1
To figure out how many days were in the Start month, we subtract the Start day from the last day of the Start month:
=(IFERROR(DATE(YEAR(Start@row), MONTH(Start@row) + 1, 1), DATE(YEAR(Start@row) + 1, 1, 1) - 1) - Start@row
Dividing that by the number of days in the month gives us our decimal for the Start month.
=((IFERROR(DATE(YEAR(Start@row), MONTH(Start@row) + 1, 1), DATE(YEAR(Start@row) + 1, 1, 1) - 1) - Start@row) / DAY(IFERROR(DATE(YEAR(Start@row), MONTH(Start@row) + 1, 1), DATE(YEAR(Start@row) + 1, 1, 1) - 1)
The decimal for our End month is a little more simple. We just take the day of the End month and divide by the total number of days in the month.
=DAY(Finish@row) / DAY(IFERROR(DATE(YEAR(Finish@row), MONTH(Finish@row) + 1, 1), DATE(YEAR(Finish@row) + 1, 1, 1) - 1)
Then finally we add those two calculations to the month formula:
=(12 - MONTH(Start@row) + (((YEAR(Finish@row) - YEAR(Start@row)) * 12) - 12) + MONTH(Finish@row)) + (((IFERROR(DATE(YEAR(Start@row), MONTH(Start@row) + 1, 1), DATE(YEAR(Start@row) + 1, 1, 1) - 1) - Start@row) / DAY(IFERROR(DATE(YEAR(Start@row), MONTH(Start@row) + 1, 1), DATE(YEAR(Start@row) + 1, 1, 1) - 1)) + (DAY(Finish@row) / DAY(IFERROR(DATE(YEAR(Finish@row), MONTH(Finish@row) + 1, 1), DATE(YEAR(Finish@row) + 1, 1, 1) - 1))
-
@Paul Newcome oh ok, I understand. However, for some unknown reason, it does not add the correct value (see pic). Can you advise? (I apologise, I am not very good at creating formulas, not this complicated!) :)
-
That was actually my fault. I realized after I posted that I had missed some parenthesis and was in the process of correcting when you asked. My apologies. Try this...
=(12 - MONTH(Start@row) + (((YEAR(Finish@row) - YEAR(Start@row)) * 12) - 12) + MONTH(Finish@row)) + (((IFERROR(DATE(YEAR(Start@row), MONTH(Start@row) + 1, 1), DATE(YEAR(Start@row) + 1, 1, 1)) - 1) - Start@row) / DAY(IFERROR(DATE(YEAR(Start@row), MONTH(Start@row) + 1, 1), DATE(YEAR(Start@row) + 1, 1, 1)) - 1)) + (DAY(Finish@row) / DAY(IFERROR(DATE(YEAR(Finish@row), MONTH(Finish@row) + 1, 1), DATE(YEAR(Finish@row) + 1, 1, 1)) - 1))
-
@Paul Newcome thanks Paul. However, it is still not showing the correct value, I think it needs a little substracted as it should be somewhere close to 11.5ish (the same value but -1, ie 11.48) (see pic). Any advice?
-
How's this (generates 12.51613 when using your above dates):
=(12 - MONTH(Start@row) + (((YEAR(Finish@row) - YEAR(Start@row)) * 12) - 12) + MONTH(Finish@row)) + ((((IFERROR(DATE(YEAR(Start@row), MONTH(Start@row) + 1, 1), DATE(YEAR(Start@row) + 1, 1, 1)) - 1) - Start@row) + 1) / DAY(IFERROR(DATE(YEAR(Start@row), MONTH(Start@row) + 1, 1), DATE(YEAR(Start@row) + 1, 1, 1)) - 1)) + (DAY(Finish@row) / DAY(IFERROR(DATE(YEAR(Finish@row), MONTH(Finish@row) + 1, 1), DATE(YEAR(Finish@row) + 1, 1, 1)) - 1))
-
I understand. I think though that it takes 1year as 13 months, so if a project is running for 5-6 years, it will cause a deviation of about 5-6 months. That's why I wondered if there is a way to get it to the approximate 11.5? (that's approximately from mid January to end Dec). Do you know what I mean?
-
I didn't even notice that bit. The error isn't actually on the number of years. For example...
1 Jan 20 - 31 Dec 20 is currently showing as 13 months, but if you look at 1 Jan 20 - 31 Dec 25 it is showing as 73 instead of adding an additional month per year. I know what the problem is, but I am not sure if I can explain it properly. The solution though is to simply subtract 1.
=(12 - MONTH(Start@row) + (((YEAR(Finish@row) - YEAR(Start@row)) * 12) - 12) + MONTH(Finish@row)) + ((((IFERROR(DATE(YEAR(Start@row), MONTH(Start@row) + 1, 1), DATE(YEAR(Start@row) + 1, 1, 1)) - 1) - Start@row) + 1) / DAY(IFERROR(DATE(YEAR(Start@row), MONTH(Start@row) + 1, 1), DATE(YEAR(Start@row) + 1, 1, 1)) - 1)) + (DAY(Finish@row) / DAY(IFERROR(DATE(YEAR(Finish@row), MONTH(Finish@row) + 1, 1), DATE(YEAR(Finish@row) + 1, 1, 1)) - 1)) - 1
-
You're an absolute star Paul. Thank you very much!!!! :)
-
Happy to help! 👍️
Sorry for the initial confusion. Glad we were able to get it working for you.
Help Article Resources
Categories
Check out the Formula Handbook template!
