Formula to add days to a date

I have a dropdown column for PM Frequency (1 month, 3 month, 6 month etc...) and date columns for last PM and next PM. I want to add the appropriate number of days to last PM to generate a date for next PM based on the PM frequency. What is the best way to write a formula for this?

Tags:

Best Answer

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓

    @Chris K

    Let me know if this needs adjusted any.

    =IF([PM Frequency]@row = "1 Month", [Last PM]@row + 30, IF([PM Frequency]@row = "3 month", [Last PM]@row + 90, IF([PM Frequency]@row = "6 month", [Last PM]@row + 180, IF([PM Frequency]@row = "9 month", [Last PM]@row + 270, IF([PM Frequency]@row = "12 month", [Last PM]@row + 365)))))

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓

    @Chris K

    Let me know if this needs adjusted any.

    =IF([PM Frequency]@row = "1 Month", [Last PM]@row + 30, IF([PM Frequency]@row = "3 month", [Last PM]@row + 90, IF([PM Frequency]@row = "6 month", [Last PM]@row + 180, IF([PM Frequency]@row = "9 month", [Last PM]@row + 270, IF([PM Frequency]@row = "12 month", [Last PM]@row + 365)))))

  • Dan W
    Dan W ✭✭✭✭✭
    edited 07/14/22

    @Chris K

    I see this has already been answered. But here is a solution that will allow any number of months to be added to the drop down. As long as it is in the format "# Months" Example: 23 Months

    =IFERROR(DATE(YEAR([last PM]@row) + INT((MONTH([last PM]@row) + INT(VALUE(LEFT([PM Frequency]@row, FIND(" ", [PM Frequency]@row) - 1)))) / 12.01), ROUND(MOD(MONTH([last PM]@row) + INT(VALUE(LEFT([PM Frequency]@row, FIND(" ", [PM Frequency]@row) - 1))), 12.01), 0), 1) + DAY([last PM]@row - 1), "")

  • Chris K
    Chris K ✭✭✭

    Magic!


    Thank you both for the help!

  • @Mike TV: Hey Mike, I saw your reply to Chris. I was trying a similar thing, where I wanted some of columns to add 7, 14, 28 or 56 days to the date put as input in some other column. The following is the function that I was trying:


    =IF([Day(s) Strength to be tested.]@row = "7 days", [Set ID]@row + 7, IF([Day(s) Strength to be tested.]@row = "14 days", [Set ID]@row + 14, IF([Day(s) Strength to be tested.]@row = "28 days", [Set ID]@row + 28, IF([Day(s) Strength to be tested.]@row = "56 days", [Set ID]@row + 56))))

    But for some reason it is not working.

    @Mike TV

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!