Weekday Formula Error?

Chris Mondeau

Hello all!

I've been trying to create a time entry sheet like many others, but am running into an error with the Weekday() formula.

I'm using the formula on a system column for the date/time the row was created. I have an entry for 01/22/18 4:20 PM, which returns "2" for the weekday value. Right after I have an entry for 01/22/18 7:23 PM, which returns "3". It caught me off guard since it's still the same day. The only thing I can think of is the system's date/time set-up is GMT or something that has the hours set for different date thresholds.

Link to Example Sheet

Mike Wilday

Yes, Smartsheet's date/time in the backend is set to Eastern where their servers exists. Calculations on Date and Time reference server time not the displayed local-time. Sorry. 

 

Chris Mondeau

Regardless of where the servers are, shouldn't the stored timestamp provide the date that the weekday formula can return correctly? I might expect the next date in sequence when the time is closer to midnight, but this wasn't the case in my tests (5 pm and 7 pm CST).

J. Craig Williams

Servers are in Pacific time zone, but Mike was on the right track.

There is (and continues to be) a known design issue that deals with the UTC time zone and how it handled by Smartsheet.

Chris McKay described it well here:

https://community.smartsheet.com/discussion/utc-date-fix-response-required

Craig

 

Mike Wilday

Thanks, for correcting that Craig. The time issue and being unable to track time correctly can be incredibly frustrating.