Help

Formula to track beginning of month OR end of month date

Solved
Jump to Solution
3817 3
cancel
Showing results for 
Search instead for 
Did you mean: 
James_Mendaros
4 - Data Explorer
4 - Data Explorer

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!

1 Solution

Accepted Solutions

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.

Screenshot 2020-01-30 at 20.57.40

JB

See Solution in Thread

3 Replies 3
James_Mendaros
4 - Data Explorer
4 - Data Explorer

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

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.

Screenshot 2020-01-30 at 20.57.40

JB

James_Mendaros
4 - Data Explorer
4 - Data Explorer

This works perfect! Thank you for your help.