Formula to track beginning of month OR end of month date

[James_Mendaros]

I currently use Zapier to ‘Timestamp’ a date when a record is uploaded onto Airtable.

What I want to know is a formula that displays a new date that is dependent on when that timestamp was placed.

For example:
If the ‘Timestamp’ shows any date before January 15th, I want the new formula to show as January 1. If the ‘Timestamp’ date is on or after January 15th, I want the formula date to show as January 31.

Thanks!

[James_Mendaros]

bump :slightly_smiling_face: I still cant seem to find the answer to this.

[JonathanBowen · Accepted solution]

Hi @James_Mendaros - the formula is a bit complicated, but can be done:

IF(
  Timestamp < DATETIME_FORMAT(Timestamp, 'YYYY-MM-16'), 
  DATETIME_FORMAT(Timestamp, 'YYYY-MM-01') , 
  DATETIME_FORMAT(DATEADD(DATEADD(DATETIME_FORMAT(Timestamp, 'YYYY-MM-01'), 1, 'month'), -1, 'days'), 'YYYY-MM-DD')
) 

A quick explanation:

IF(
  Timestamp < DATETIME_FORMAT(Timestamp, 'YYYY-MM-16')

If the timestamp is less than the 16th of the timestamp month…

DATETIME_FORMAT(Timestamp, 'YYYY-MM-01')

then return the 1st of the timestamp month…

DATETIME_FORMAT(DATEADD(DATEADD(DATETIME_FORMAT(Timestamp, 'YYYY-MM-01'), 1, 'month'), -1, 'days'), 'YYYY-MM-DD')

otherwise return the first day of the next month minus 1 day (formatted as yyyy-mm-dd).

This last bit is needed because the last day of the month isn’t always the same number and it is much easier to calculate the 1st of the next month and go back 1 day than figure out short months, leap years and so on.

JB

[James_Mendaros]

This works perfect! Thank you for your help.