Calculation of monthly duration

Options
✭✭✭✭✭✭

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?

Tags:

• ✭✭✭✭✭✭
Options

Give something like this a try...

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
edited 04/14/20
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
edited 04/14/20
Options

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?

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!