Formula to calculate and copy dates based on form submissions and standard timeframes

Francesca Gallisai · 03/04/24

I am very new to Smartsheet and I am struggling to find the right formula for a sheet. I am working on a access sheet linked to a form. Different access categories have standard timeframes, for others, the person submitting the form will have to indicate a specific date. I was planning to have a column where to have all the dates under control and planned to use the IF function, let's call it the date summary column. For example, if in a row the Demo account is selected I want to have 30 days added to the submission form to show up to the date summary column, if a contributor account is selected and the date is sent by the person requesting the access, I want that date copied to the date summary column, if the select access is C, I want to get a date that is 8 months ahead the submission date.

This is what I came up so far but, as you might have guessed already is not working:

=IF([Category]@row = "Demo account", DATE([Submission]@row) + 30

Other things to consider:

I already set up a column called "Submisison" that automatically registers the date and time of a request
There will be multiple dates, including extension request, so ideally I would need a formula that can be adapted to different cells

Thank you in advance!

KPH · 03/05/24

The DATE function expects you to supply years, months, and days separated by commas, like (DATE(24,1,1). It is not expecting a date. If your Submission column contains a date (which it will if this is the system generated created date) then all you need is:

=IF(Category@row = "Demo account", Submission@row + 30)

To extend this for your other windows, you can add those in the value-if-false location of the first IF, remembering to close the parentheses for them all at the end.

=IF(Category@row = "Demo account", Submission@row + 30,

IF(Category@row = "C", Submission@row + 243,

IF(Category@row = "Contributor account", Submission@row

)))

I have used 243 days for 8 months as it is easier than adding 8 months. If you need to be more precise, let me know and I will share a formula for adding exact months.