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

Tags:

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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)

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!