Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

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

Topic Labels: Formulas
Solved
Jump to Solution
2345 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Jevon_Stammers
5 - Automation Enthusiast
5 - Automation Enthusiast

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…

1 Solution

Accepted Solutions
Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

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

See Solution in Thread

4 Replies 4
Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

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

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!

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.

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