How to truly get "Last Month"

In the article for the MONTH() expression, it states you can use MONTH(TODAY())-1 to get the integer value for last month. So you could also use MONTH(TODAY())-2 to get the month integer from 2 months ago, etc…

Problem is, that isn’t true. If it’s January (month 1) and you use MONTH(TODAY())-1 and expect to get December (12), you’d be wrong. The article for the MONTH() expression is misleading on how to get last month, or 2 months ago, etc…

So, to truly get Last Month, you would use ABS(12+MONTH(TODAY())-1) and you can change that “-1” to go back more months.

This way if it’s currently January, the formula will result in 12, for December, as expected.

**EDIT: USE THE CORRECT EXPRESSION BELOW PROVIDED BY STEVE

2 Likes

You are correct that the help doc doesn’t take into account the turn of the year, but your expression has its own limitations. If today is in February, ABS(12+MONTH(TODAY())-1) = ABS(12+2-1) = ABS(13) = 13. If today is in December, ABS(12+MONTH(TODAY())-1) = ABS(12+12-1) = ABS(23) = 23. So your expression is only correct for January.

Better:

MONTH(EOMONTH(TODAY(), -1))

Or:

(MOD((MONTH(TODAY()) - 1 + 12 - 1), 12) + 1)

(-1 for 0-11 instead of 1-12, +12 to allow for Jan to Dec, -1 for last month, +1 back to 1-12 from 0-11)

Which simplifies to:

(MOD((MONTH(TODAY()) + 10), 12) + 1)
4 Likes

Thank you Steve! You’re absolutely right. I was too hasty in writing my expression specific to January.

@Greg_L, I’ve corrected the doc. Thanks for bringing this to our attention!

5 Likes

So if I want to select only the entries of a table that have occured in the previous 31 days or 7 days (month or week), how would I do that?

Try:

FILTER(
  "Table",
  ([Date] >= (TODAY() - 31))
)

Or:

FILTER(
  "Table",
  ([Date] >= (TODAY() - 7))
)
2 Likes

Wow thanks for the quick response! I will try that.

1 Like

@Steve I would love to see AppSheet create a DATEADD() expression that allows you to easily add (or subtract) time in different types of units (minutes, days, hours, months, etc…)

It would look like this: DATEADD([date], [#], ‘units’)

So… DATEADD(TODAY(), 10, ‘days’) returns a date that is 10 days after today.

See the way it is used over at Airtable: https://support.airtable.com/hc/en-us/articles/203255215-Formula-field-reference#date_and_time_functions