datedif substitute in smartsheet

CLMini
CLMini
edited 10/01/22 in Formulas and Functions

Please can someone help with formulas for the attached datedif, which allocates the correct number of months into each year by column.

excel attached, which works!

thank you so much in advance!!


Answers

  • Hi @CLMini

    Smartsheet does not currently have a DATEDIF function that will automatically calculate this for you.


    The way I would find this calculation is by using the MONTH and YEAR functions to pull the numbers from your dates.

    For your TOTAL column:

    =MONTH([End Date]@row) + (13 - MONTH([Start Date]@row)) + (((YEAR([End Date]@row) - YEAR([Start Date]@row) - 1) * 12) - 1)


    Then for your date-specific columns, you'll need to use an IF statement to identify the year you're looking for. E.g. for 2022, I would first check to see if both dates are in 2022, If they are, we simply need to subtract the end month from the start month:

    =IF(AND(YEAR([Start Date]@row) = 2022, YEAR([End Date]@row) = 2022), MONTH([End Date]@row) - MONTH([Start Date]@row),

    Then we can check if the End date is in 2022 (but the Start date isn't). If so, we only need the Month number of the End Date:

    IF(YEAR([End Date]@row) = 2022, MONTH([End Date]@row),

    Finally, if neither of these statements are true, but the Start Date is in 2022, then we can subtract this month off of 12 to find out how many months are in 2022 from the Start of the task.

    IF(YEAR([Start Date]@row) = 2022, 12 - MONTH([Start Date]@row),

    And if none of these are true (neither date has 2022), then the cell should show 0:

    0)))


    For a full formula:

    =IF(AND(YEAR([Start Date]@row) = 2022, YEAR([End Date]@row) = 2022), MONTH([End Date]@row) - MONTH([Start Date]@row), IF(YEAR([End Date]@row) = 2022, MONTH([End Date]@row), IF(YEAR([Start Date]@row) = 2022, 12 - MONTH([Start Date]@row), 0)))


    For your other Year columns you'd just need to swap out the written in 2022 for the next year:

    =IF(AND(YEAR([Start Date]@row) = 2023, YEAR([End Date]@row) = 2023), MONTH([End Date]@row) - MONTH([Start Date]@row), IF(YEAR([End Date]@row) = 2023, MONTH([End Date]@row), IF(YEAR([Start Date]@row) = 2023, 12 - MONTH([Start Date]@row), 0)))


    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!