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

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”)))))

image

@nguz ,

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

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

2 Likes

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”).

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.