Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 21, 2020 08:44 AM
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…
Solved! Go to Solution.
May 21, 2020 09:46 AM
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
May 21, 2020 09:46 AM
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
May 21, 2020 10:03 AM
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!
May 21, 2020 10:06 AM
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.
May 21, 2020 10:13 AM
Perfect - thanks again @Jeremy_Oglesby That had been bugging me for hours!