Why is 'DAY' Value from a Date being populated incorrectly?

I have a 'Formatted Date' column with a column formula (below) that formats the date from the row's date timestamp, which I renamed to 'Training Completion Date', into DD-MMM-YYYY format. All rows but one is getting the correct day from the DATE. I cannot figure out why 10/08/24 returns 9-Oct-2024. Curious to know if anyone else has had this issue and what a potential resolution is.
Formula is:
=DAY(([Training Completion Date]@row)) + "-" + IF(MONTH([Training Completion Date]@row) = 1, "Jan") + IF(MONTH([Training Completion Date]@row) = 2, "Feb") + IF(MONTH([Training Completion Date]@row) = 3, "Mar") + IF(MONTH([Training Completion Date]@row) = 4, "Apr") + IF(MONTH([Training Completion Date]@row) = 5, "May") + IF(MONTH([Training Completion Date]@row) = 6, "Jun") + IF(MONTH([Training Completion Date]@row) = 7, "Jul") + IF(MONTH([Training Completion Date]@row) = 8, "Aug") + IF(MONTH([Training Completion Date]@row) = 9, "Sep") + IF(MONTH([Training Completion Date]@row) = 10, "Oct") + IF(MONTH([Training Completion Date]@row) = 11, "Nov") + IF(MONTH([Training Completion Date]@row) = 12, "Dec") + "-" + YEAR([Training Completion Date]@row)
Edit: 5:11 PST: Out of curiosity, I submitted a test entry in my form after 5PM to see what would happen. The DAY returned tomorrow, 10, instead of today. Why is this happening and how can I return the actual DAY.
Thank you.
-Joel
Best Answer
-
Hi @jmarkovi,
The problem is that SmartSheet stores the dates using the GMT timezone (Greenwich Mean Time) rather than storing them using your local timezone. So for example, if your timezone is PST then it is 7 hours behind GMT. If your timezone is PST and Training Completion Date shows 5:14 PM, then behind-the-scenes SmartSheet is storing it as 5:14 PM + 7 hours which is 12:14 AM on the next day. This explains why using DAY gives you the next date.
If you and all of your team are using the same timezone, a quick solution is to just subtract a certain number of hours from the time, and then using that adjusted number in your DAY function. If you are in PST then you can just subtract 7 hours.
Here is another useful thread:
Best!
Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)
SSFeatures - The browser extension that adds essential features into Smartsheet.
Answers
-
Hi @jmarkovi,
The problem is that SmartSheet stores the dates using the GMT timezone (Greenwich Mean Time) rather than storing them using your local timezone. So for example, if your timezone is PST then it is 7 hours behind GMT. If your timezone is PST and Training Completion Date shows 5:14 PM, then behind-the-scenes SmartSheet is storing it as 5:14 PM + 7 hours which is 12:14 AM on the next day. This explains why using DAY gives you the next date.
If you and all of your team are using the same timezone, a quick solution is to just subtract a certain number of hours from the time, and then using that adjusted number in your DAY function. If you are in PST then you can just subtract 7 hours.
Here is another useful thread:
Best!
Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)
SSFeatures - The browser extension that adds essential features into Smartsheet.
-
Thank you!! I was able to use: =VALUE(MID(Created@row + "", 4, 2)), replacing 'Created' with the my revised column name to get the correct day. Appreciate this help!!
-
You're welcome, I'm glad that the fix was super smooth!
Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)
SSFeatures - The browser extension that adds essential features into Smartsheet.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 431 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!