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

Tags:

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

• 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!