Using a formula to return the date of 1st day of last month

[Jevon_Stammers]

Hi all,

New to Airtable and by no means a whizz when it comes to formulas.

In short, I need to create a formula that will return a date of the 1st day of last month. In other words, today (May 21st), the field should show a date of April 1st 2020. As soon as we hit June, that date should change to May 1st 2020.

Any idea how I could do this?

Thanks all…

[Jeremy_Oglesby · Accepted solution]

Hi @Jevon_Stammers,

I think this formula should do that for you:

DATEADD(DATEADD(TODAY(), -1, 'month'), -(DAY(TODAY()) - 1), 'days')

This formula first subtracts 1 month from the current date (the inner DATEADD()), and then on the resulting date returned from that, it subtracts a number of days equal to the current date minus 1.

So essentially, it will always do this to the current date:

05 / 21 / 2020     08 / 06 / 2021     01 / 01 / 2022
-1 /-20 / -0       -1 / -5 / -0       -1 / -0 / -1 (since -1mo. requires the prev. yr.)
--------------     --------------     --------------
04 / 01 / 2020     07 / 01 / 2021     12 / 01 / 2021

[Jevon_Stammers]

Hi @Jeremy_Oglesby,

Thanks for that - it’s working perfectly. And am I right in thinking that this would still work on the 1st of each month? My understanding of the logic is that it would try to subtract zero from the days (i.e. 1 - 0 = 1)?

Thanks again!

[Jeremy_Oglesby]

Yep – it would attempt to subtract the value 0 from the value 1 for the “day” portion of the date, resulting in the “day” portion staying at 1.

Thanks for pointing that out – I’ll add it to the examples in my post for reference to future readers.

[Jevon_Stammers]

Perfect - thanks again @Jeremy_Oglesby That had been bugging me for hours!