Date formula previous month for a new year

B Young

I'm still getting my head around date formulas, I have one to tell me the start date of the previous month:

=IFERROR(DATE(YEAR([Date Column]@row), MONTH([Date Column]@row) - 1, 1), DATE(YEAR([Date Column]@row) + 1, 1, 1))

This worked fine until we changed to a new year, what have I done wrong with this formula?

Paul Newcome

Yes. The first MONTH function should be minus one. Not minus twelve.

Paul Newcome

The second DATE function needs a couple updates. Change the year to be minus 1 instead of plus 1 and the month to 12 instead of 1.

B Young

Thank you - I have adjusted to the following =IFERROR(DATE(YEAR([Date Column]@row), MONTH([Date Column]@row) - 12, 1), DATE(YEAR([Date Column]@row) - 1, 1, 1))

If the [Date Column] says a date in January 2023 it's showing 1st January 2022 instead of 1st December 2022 - have I done it correct to your suggestion?

Paul Newcome

Right. The second change I suggest should take care of that. Change the month number in the second DATE function from 1 to 12.

B Young

Thank you appreciate your help! Does this mean the formula will need to be changed back again after the end of this month? There isn't a formula that would calculate the previous month even in January of a new year?

Paul Newcome

You shouldn't need to change anything. That is why there is two separate DATE functions.

The first one will run to grab the typical month minus 1. If that throws an error (in January) then the second DATE function will run.

B Young

Ok thank you that's interesting - do I still do have the formula wrong then? This is what I have now:

=IFERROR(DATE(YEAR([Date Column]@row), MONTH([Date Column]@row) - 12, 1), DATE(YEAR([Date Column]@row) - 1, 12, 1))

If I put in the [Date Column] today's date (12/1/2023) then it correctly returns 1/12/2022

But if I put in [Date Column] 11/2/2023 (or a date in Feb 2023) it still returns 1/12/2022

Paul Newcome

Yes. The first MONTH function should be minus one. Not minus twelve.

B Young

Ok thank you! So sorry for the misunderstanding. This works perfectly now! Very much appreciate your help!

Paul Newcome

No worries. Happy to help. 👍️