Beware behind the scenes Date Format
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
Comments
-
Thanks for getting to the bottom of this Craig.
RichardR
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!