Formula Column (used for Dates) is off by 1 day

[nguz (Nelson G.)]

Hello,
I’m using multiple formula columns to identify Months and Quarters (Qtrs) from Date columns, and the results are always off by one (1) day.

The result is an incorrect outcome, for example, if the date is Feb 1, 2021. The formula is currently reading the value = Jan 31, 2021. Therefore instead of being month =2, is 1.

I’ve also tried adding the time to rule out it is something related to time zones, but the result is the same.

Please advise.

Here’s an example formula:

IF({Planned Date Sub}=“”, “”, IF(Month({Planned Date Sub}) <= 3, “Q1”, IF(Month({Planned Date Sub}) <= 6, “Q2”, IF(Month({Planned Date Sub}) <= 9, “Q3”, IF(Month({Planned Date Sub}) <= 12, “Q4”)))))

[JCorrell (Jim - The Monday Man)]

@nguz ,

Try changing “Month(” to “MONTH(”.

If anyone knows WHY this would matter, I’d love to know.

[nguz (Nelson G.)]

Thanks, @JCorrell. It works.

I didn’t think it’d matter as other formulas works the same in lower and upper caps (for example “If”).