Adding hours to a date

Khari Shiver

Hello,

I have the following columns in a sheet:

"Created (Date)" records the date/time a form was submitted.

"Modified (Date)" records the date/time a row is copied to the final sheet in my workflow.

I want to compare the modified date to another date that is manually entered in the row to determine when to send a notification. I'm aware that while the two columns that automatically record date/time stamps show a date that is based on the owner's time zone which is in this case EST. If I use a formula like weekday() or dateonly(), those formulas reference the date on the server which is 4 hours ahead of EST. I've created another column called "Modified (Date) calc" that extracts the date from the time/date stamp and another column that I would like to use to subtract 4 hours to account for the difference between PST and the server's GMT time zone.

I thought the formula =[Modified (Date) calc - 4/24] would work but I'm getting a date that I don't understand.

In my example:

"Modified (Date):" 06/27/21 12:25PM

"Modified (Date) calc:" 6/27/21 - I used DATEONLY() to extract the date.

The formula in bold above is giving me a date of 6/26/21 which I don't understand.

Any help would be appreciated.

Thank you,

Khari

Kelly Moore

The documentation says it's based on sheet owner. I keep the helper columns hidden so I'm not sure how it would appear to my users. Since the dates are now all relative to one another, vs the timestamp where they may not be, the calculation of number of days is more accurate. I hope this method works for you - it has solved all of my timezone problems.

cheers,

Kelly

Kelly Moore

Hello @Khari Shiver

Other than occasionally the day being converted to the next day because of the timezone issue, are you tracking total time difference in hours, or in days?

As a different work-around, if dates are all you need, consider using the automated routine Record a Date, which is based on triggers you define (such as creation of new row). You would need a couple of helper date columns to house the date recorded but this date is recorded in the correct timezone consistently- not the server time, thus the time difference should go away. I use the Record Date automation anytime I'm trying to correct for timezones as a fast workaround. Record Date does not record the time portion- if that is important search the community for posts on timezone issues.

If you decide to go forward with manually trying to workaround the timezone issue I think in your above equation you wanted something like this? Because the time recorded is in the system columns are recorded in a 12hr clock vs a 24hr clock, I'm not sure this formula will give you the results you expect. The 4hrs would need to be adjusted from the time portion of the datestamp

=[Modified (Date) calc]@row-4/24

The community may offer additional advice.

https://help.smartsheet.com/articles/2481986-set-the-current-date-with-record-a-date-action

Khari Shiver

I've never even noticed the "Record a date" option in the workflows. THANK YOU!

Khari Shiver

One follow up question... As the sheetowner is in PST, if an entry is made in another timezone, will the date adjust automatically based on where it was entered or will it always be in PST?

Kelly Moore

The documentation says it's based on sheet owner. I keep the helper columns hidden so I'm not sure how it would appear to my users. Since the dates are now all relative to one another, vs the timestamp where they may not be, the calculation of number of days is more accurate. I hope this method works for you - it has solved all of my timezone problems.

cheers,

Kelly