Setting a date based upon another date

Dan Foster

Hi everyone, long time Smartsheet user first time poster here.

I am seeking help in setting a date column based upon another date column.

Put simply, I have a "date received" column and a "created" column, where the created column populates automatically based upon the time a submission was made using a form. The "date received" column I have been manually setting, as any work received after close of business (5:00pm) we consider as being received the following day.

So I'm seeking help in writing a formula that essentially says "If (created) is greater than 5:00pm and less than 11:59pm, set (date received) to tomorrow. If (created) is earlier than 5:00pm, set (date received) to today".

Paul Newcome

Try something like this...

Create a helper column that is text/number and enter this formula...

=SUBSTITUTE([Created Date]@row, DATEONLY([Created Date]@row), "")

Then in the date column we would use...

=DATEONLY([Created Date]@row) + IF(AND(VALUE(LEFT([Helper Column]@row, FIND(":", [Helper Column]@row) - 1)) <> 12, VALUE(LEFT([Helper Column]@row, FIND(":", [Helper Column]@row) - 1))>= 5, FIND("P", [Helper Column]@row)> 0), 1)

Basically if we find a "P" and the hour is greater than 5 but not equal to 12 (accounts for 12pm/noon), then add 1 to the Created Date.

Kelly Moore

Hey Dan and Paul

I believe this is the same timezone problem I encountered earlier last year in a few of my sheets. I found between the time of midnight of timezone GMT/UTC=0 (which is how the timestamp is actually stored) and midnight of the local time, the DATEONLY(Created@row), "") portion of the timestamp cannot be found since it's a different day.

For the time portion of the formula, you can use =RIGHT(Created@row, (LEN(Created@row) - 9)) and it will work.

The date is trickier - you can use the LEFT function and do the same thing but the date returned is the GMT=0 date, not necessarily the local time date. If that doesn't matter, use the LEFT function. If it does matter, you might want to consider adding a helper Date column and using the new Record a Date automation, triggering off of either a new row or changing value in Created field (which is essentially same thing). You could then use the Paul's earlier formula which changes the date based on 5pm, but looking at this new helper date column as your Date source.

PS- since I was needing a solution prior to the Record a Date automation, I was able to use Bridge (premier type App from Smartsheet) to push the correct local date into a helper column on my sheet.

cheers,

Kelly

Paul Newcome

Try something like this...

Create a helper column that is text/number and enter this formula...

=SUBSTITUTE([Created Date]@row, DATEONLY([Created Date]@row), "")

Then in the date column we would use...

=DATEONLY([Created Date]@row) + IF(AND(VALUE(LEFT([Helper Column]@row, FIND(":", [Helper Column]@row) - 1)) <> 12, VALUE(LEFT([Helper Column]@row, FIND(":", [Helper Column]@row) - 1))>= 5, FIND("P", [Helper Column]@row)> 0), 1)

Basically if we find a "P" and the hour is greater than 5 but not equal to 12 (accounts for 12pm/noon), then add 1 to the Created Date.

Dan Foster

Thank you very much for this feedback Paul, I'll take a crack at this this afternoon.

Paul Newcome

Happy to help. 👍️

Dan Foster

Hi Paul,

This seems to be right on the right track, however I've run into one snag, where the "helper" column you suggested seems to not work properly for any entry later than 4pm. Do you have any suggestions on how to get around this?

Paul Newcome

Can you copy/paste the exact formula from your sheet to here?

Dan Foster

I basically copied exactly what you suggested, with the "Date Helper" column containing:

=SUBSTITUTE(Created@row, DATEONLY(Created@row), "")

And the "Test Due Date" column containing:

=DATEONLY(Created@row) + IF(AND(VALUE(LEFT([Date Helper]@row, FIND(":", [Date Helper]@row) - 1)) <> 12, VALUE(LEFT([Date Helper]@row, FIND(":", [Date Helper]@row) - 1)) >= 5, FIND("P", [Date Helper]@row) > 0), 1)

Paul Newcome

I wonder if maybe the SUBSTITUTE function needs adjusted. Technically there is a space between the date and time and we are only removing the date, not the space after.

=SUBSTITUTE(Created@row, DATEONLY(Created@row) + " ", "")

Kelly Moore

Hey Dan and Paul

I believe this is the same timezone problem I encountered earlier last year in a few of my sheets. I found between the time of midnight of timezone GMT/UTC=0 (which is how the timestamp is actually stored) and midnight of the local time, the DATEONLY(Created@row), "") portion of the timestamp cannot be found since it's a different day.

For the time portion of the formula, you can use =RIGHT(Created@row, (LEN(Created@row) - 9)) and it will work.

The date is trickier - you can use the LEFT function and do the same thing but the date returned is the GMT=0 date, not necessarily the local time date. If that doesn't matter, use the LEFT function. If it does matter, you might want to consider adding a helper Date column and using the new Record a Date automation, triggering off of either a new row or changing value in Created field (which is essentially same thing). You could then use the Paul's earlier formula which changes the date based on 5pm, but looking at this new helper date column as your Date source.

PS- since I was needing a solution prior to the Record a Date automation, I was able to use Bridge (premier type App from Smartsheet) to push the correct local date into a helper column on my sheet.

cheers,

Kelly

Dan Foster

Thank you both very much for your feedback! It took some fiddling, but I was able to get this to work

Paul Newcome

Happy to help. 👍️

APell

I know this is an older issue. But I found a cure. Create a new column with date format (Local Date and Time). Create a workflow automation. When 'creation date' is 'not blank' record the date in 'local date and Time'.

The workflow automation time stamps are local time for the user. So, technically speaking, you dont even need the form creation date and time anymore. You could use the workflow on the first form response or first column entry.