Month() function/formula evaluates February 1st wrong, Month(February 1) => 1 but should be 2

The Formula column Month() function is supposed to evaluate to the number of the month of the year. Month(January 10) => 1

However, I get an invalid result for February 1st.
I have not verified with all 364 other days of the year, but it would be nice if we could know that this evaluates correctly.

Working correct for me, I guess this is a Timezone issue. Remember the data is stored in UTC and showing in local TZ. As far as I know the formula month() does not take TZ in consideration.

Thank you for your reply @basdebruin!

That’s it!

Unfortunately the team doesn’t want the time of day added to the column though.

hi @APICaptain

ohh, the update I got in the mail is little bit different than the one in the community (did you edit it?)

Anyhow, I am in CET which is UTC+1 (or+2 in summer), therefore my window where the date on my clock differs from the stored date (in UTC) is small. I guess you can test it yourself by setting your TZ in monday to UTC and see if the date column still shows Feb 1. My 2 cents is that is will show Jan 31.

Can you subtract a fixed number of hours in the formula? Tricky (if at all possible) because than you make your workflow TZ specific.

Yes I edited it. I replied quickly, then tested the theory and edited my initial response with the results.

Thanks for your reply!

I don’t think there’s a way to manipulate the value of the date data in that way with the formula column. If you can think of way, I’m all ears!

Automatically adding a time and having it default after 7:00am fixes this, but like I said, we don’t want the time added to the Due Date column.

This seems to be a legitimate bug to me. No matter what time zone a workspace is in, time data should evaluate correctly and in the time zone of the workspace.

I did some more testing and actually if I set a time, even 12:00am, the issue is resolved.

If I just pick a date, no time, the issue persists.

Could you please see if this is a known issue or if you can theorize a workaround?

Can you do something like this?

MONTH(CONCATENATE({Date}, " 00:00"))

I can’t actually recreate the issue, but concatenating at time might just dupe it into doing what you expect.

I’d hesitate to call this a bug. I can’t quite think of all the possible scenarios where this could come into play, but I don’t think it’s necessarily unexpected or incorrect behavior.

EDIT: Or even better, just set the month to the 15th in all cases:


Thanks a bunch for your reply @FrancisElliott!

This is a solid workaround.

As for the bug comment,
The month() function does not evaluate correctly for the date column’s default value for Feb 1 without setting a time of day.
This is an unexpected and incorrect evaluation that requires a workaround as far as I’m concerned.

1 Like