Beware behind the scenes Date Format

J. Craig Williams

Background:

A colleague of mine ran into a problem with Dashboards being fed from a Summary sheet which was pulling data from Main sheets via X-Sheet References.

The Dashboards were showing incorrect values until the Summary sheet was refreshed (opened)

Investigation reveals (I believe) that the issue is due to how Smartsheet handles the Date Format behind the scenes.

Long Story Short:

Colleague is in the UK. UK date format is DD/MM/YY. She was counting rows matching certain date criteria using RIGHT(@cell,5) to get MM/YY. 

(This was attempting to avoid the error that MONTH(blank) returns)

When the Summary sheet was not open, however, the count returned by her formula was incorrect. And oddly so. 

In the background, Smartsheet appears to be using US date format, regardless of the user date format settings. So, DD/YY instead of MM/YY was being returned.

I have attempted to escalate my colleagues support ticket (as their answer was incorrect), however, I doubt this is something that can or will be fixed. Thus this post.

When a sheet is closed, the underlying Date Format 'reverts' to US date format. I don't believe there is an account setting for default date format, so when the Sheet is closed, the dates are in US format, because why not? 

I can see a few ways to fix this, but most (all) are not fool proof. 

Be careful when using Dates in formulas. Do not expect the formula to be in a certain format, because it might change when the sheet is closed or some other user opens it.

Craig

 

Richard Rymill SBP

Thanks for getting to the bottom of this Craig. 

RichardR