How do I automatically calculate a due date based on the timeframe a form has been submitted?

Some background:

I receive requests for funding through a form that then auto-populates into my sheet. On the first of every month, 3 individuals receive an email notifying them that the applications from the previous month are available for review. The due date to review them is the one week from the day they get the notification email.

Example: An application submitted for funding from January 1-January 30th will have a review date of February 7th. An application submitted for funding from June 1 - June 30th will have a review date of July 7th.

How can I make these review dates autogenerate based on the timeframe the form was submitted? Not necessarily the exact date.

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Ok. SO the 7th of the month after submission would look something like this (in a Date type column):

    =DATE(YEAR([Submission Date]@row) + IF(MONTH([Submission Date]@row) = 12, 1, 0), IF(MONTH([Submission Date]@row) = 12, 1, MONTH([Submission Date]@row) + 1), 7)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!