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

Answers

  • Paul Newcome
    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
    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
    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
    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
    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
    Michaela Kamenska ✭✭✭✭✭✭
    edited 04/14/20

    @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
    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
    Michaela Kamenska ✭✭✭✭✭✭
    edited 04/14/20

    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?


  • Michaela Kamenska
    Michaela Kamenska ✭✭✭✭✭✭

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!