Calculation of monthly duration

Michaela Kamenska

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?

Paul Newcome

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

Paul Newcome

Give something like this a try...

=12 - MONTH(Start@row) + (((YEAR(Finish@row) - YEAR(Start@row)) * 12) - 12) + MONTH(Finish@row)

Michaela Kamenska

@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?

Paul Newcome

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))

Michaela Kamenska

@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!) :)

Paul Newcome

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))

Michaela Kamenska

@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?

Paul Newcome

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))

Michaela Kamenska

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?

Paul Newcome

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

Michaela Kamenska

You're an absolute star Paul. Thank you very much!!!! :)

Paul Newcome

Happy to help! 👍️

Sorry for the initial confusion. Glad we were able to get it working for you.