Why is MONTH returning the wrong month?
I'm using the MONTH function against a Created system column. This Created column has date and time. For values on the last day of the month after 8pm MONTH is returning the next month.
For example: Created = 9/30/2021 8:52pm MONTH returns 10
I'm guessing that this is because the system column is actually storing the time based on the time zone the server is in, or perhaps universal time, and then that is adjusted to my time zone when it is displayed, but the MONTH (and probably DAY) functions aren't taking that into account.
Has anyone else experience this? Is there maybe a setting somewhere for the account that can fix it?
Thanks
Todd
Best Answers
-
Unfortunately this is a known bug. If you do a search here in the Community for time zone issues, you should be able to find a thread or two with a solution.
Basically you will end up having to use an IF statement to evaluate the time and date and subtract 1 from the month number
IF(AND
the hour is greater than or equal to 8,
the hour is less than 12,
FIND("PM", [Timestamp Column]@row) > 0
the DATE is the last day of the month)
-
Thanks
Answers
-
Unfortunately this is a known bug. If you do a search here in the Community for time zone issues, you should be able to find a thread or two with a solution.
Basically you will end up having to use an IF statement to evaluate the time and date and subtract 1 from the month number
IF(AND
the hour is greater than or equal to 8,
the hour is less than 12,
FIND("PM", [Timestamp Column]@row) > 0
the DATE is the last day of the month)
-
Thanks
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!