Date formula previous month for a new year

Options
✭✭

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?

Tags:

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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

• ✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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.

• ✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭
Options

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

• ✭✭✭✭✭✭
Options

No worries. Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!