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

Faith

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.

Paul Newcome

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)

Paul Newcome

Exactly how are you capturing the timeframe?

Faith

@Paul Newcome All I have right now is

1. A column that lists the date each application was submitted.
2. A simple workflow that is set to email my three reviewers on the first of each month to go in and look at what has been submitted since the last time. This workflow is not an "if applications were submitted" set up because its a guarantee in my role that there will always be new ones.

Basically I need the review due dates to be on the 7th of each month after whatever month they were submitted in.

Paul Newcome

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)

Faith

@Paul Newcome Wow! It worked, you're a genius. Thanks!

Paul Newcome

Happy to help. 👍️