Formula - Find last day of last month from Today

[Steven_Joiner]

I need a formula to find the last day of last month, no matter what day it is in the current month.

Example, if today is May 10, I need to return April 30th. If today is May 15, I need to return April 30th. If today is June 30, I need to return May 31.

I guess at some level I have to be concerned about February and Leap years, but for this purpose, I can work around that by always finding the 28th?

Additionally, I’d like to find the last day of the month two months before and 3 months before.

Thanks for any help!

[Jeremy_Oglesby]

Last day of last month:

SET_TIMEZONE(
  DATEADD(
    SET_TIMEZONE(
      TODAY(),
      'America/Phoenix'
      ),
    -DATETIME_FORMAT(
      SET_TIMEZONE(
        TODAY(),
        'America/Phoenix'
      ),
      'D'
    ),
    'day'
  ),'
  America/Phoenix'
)

If you have that formula in a Field called “Last Month”, then for 2 months ago you could say:

SET_TIMEZONE(DATEADD({Last Month}, -1, 'month'), 'America/Phoenix')

And for 3 months ago:

SET_TIMEZONE(DATEADD({Last Month}, -2, 'month'), 'America/Phoenix')

Make sure you have “Use the same time zone (GMT) for all collaborators” unchecked in the formatting options so that your timezone is always used via the SET_TIMEZONE functions. Without those functions, you sometimes get a date that is off by 1 day. Alternatively, you could make sure that every single date field in your table has the “Use same time zone (GMT)…” checked on.

Anywhere you see ‘America/Phoenix’, you’ll have to replace it with your timezone from this list:

Airtable - Support

Supported timezones for SET_TIMEZONE

The SET_TIMEZONE function will allow you to set the timezone for the data from a date type field. This is written in the form "SET_TIMEZONE(datetime, 'timezone identifier')," in which the...

[W_Vann_Hall]

Pretty clever, @Jeremy_Oglesby.

I was going to suggest something like

DATEADD(
    SET_TIMEZONE(
        DATETIME_PARSE(
            MONTH(
                TODAY()
                )&
            '/1/'&
            YEAR(
                TODAY()
                ),
            'M/D/YYYY'
            ),
        'America/Los_Angeles'
        ),
    -1,
    'd'
    )

but I’d never have thought of subtracting today from today to get the end of last month.

[Danny_White]

Hi @Jeremy_Oglesby / all,

I am trying to get the formula for a similar problem - I want to display 5pm on the last day of the current month.

Example if today is 10 may 2018, I want to display ‘May 31, 2018 05:00pm’

Any help much appreciated.

thanks

[Jeremy_Oglesby]

Assuming you always want this relative to “today”, whatever today may be, this will do it:

DATETIME_PARSE(
   DATETIME_FORMAT(
      DATEADD(
         DATEADD(
            TODAY(),
            1,
            'month'
         ),
         -DAY(TODAY()),
         'day'
      ),
   'L'
   ) 
   & " 17:00", 
   'M/D/YYYY HH:mm'
)

If you are wanting this to evaluate relative to the date in a field in your table, just replace both instances of TODAY() in the formula above with a reference to your Date field. I don’t think you’ll have Timezone issues with this one since you are manually forcing the time in from a string, so no need to use SET_TIMEZONE().