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.
@benjaminl,
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:
MONTH(CONCATENATE(LEFT({Date},8),“15”))
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.
hi @codyfrisch
I’m afraid you will see the same behavior East of GMT expect Feb 1 will show correctly as month 2, but Mar 31 will then show as month 4.
HAHA I didn’t realize this wasn’t my issue with the WEEKNUM() because it is so similar!
