Skip to main content

Current Month formula grabbing last entry of last month

  • October 9, 2023
  • 4 replies
  • 49 views
P
Forum|alt.badge.img+6

I have the following formula to set the entry for "Current Month":

 

DATETIME_FORMAT({🟢 Start Time}, 'YYYY-MM') = DATETIME_FORMAT(NOW(), 'YYYY-MM')

 

It seems to be working just fine, however the last entry from last month is setting the formula to 1.

If I change the start time (right now) to 6:30pm or anything older, the formula gets set to 0. My first thought was a timezone issue, but I confirmed on the Start Time field that it is set to my timezone (CDT).

Am I missing something obvious?

4 replies

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • October 10, 2023

Referencing a date field containing time in Formula will basically result in UTC/GMT time, regardless of the timezone setting in the date field. And if without time, the date wouldn't have changed.
Currently the SET_TIMEZONE function does not work. (This feature seems to have been disabled since the time zone format setting was added to the Date type.)

In this case, you can use the Dateadd function to correct the time difference.

DATETIME_FORMAT(DATEADD({🟢 Start Time}, -4, 'hours'), 'YYYY-MM') = DATETIME_FORMAT(NOW(), 'YYYY-MM')

You need Change add hours your time difference.

P
Forum|alt.badge.img+6
  • petebocken
  • Author
  • New Participant
  • October 10, 2023

Thanks @Semsongleng-KH!  Here is what worked for me ( @Sho SET_TIMEZONE appears to be working fine for me)

 

DATETIME_FORMAT(SET_TIMEZONE({🟢 Start Time}, 'America/Chicago'), 'YYYY-MM') = DATETIME_FORMAT(NOW(), 'YYYY-MM')

 

 

I also have a "Is Last Month" formula:

 

DATETIME_FORMAT(SET_TIMEZONE({🟢 Start Time}, 'America/Chicago'), "YYYY-MM") = DATETIME_FORMAT(DATEADD(NOW(), "month", -1), "YYYY-MM")

 

The "month" formula:

 

DATETIME_FORMAT({🟢 Start Time},'MM')

 

 

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • October 10, 2023

Oh i see, so SET_TIMEZONE function requires together with DATETIME_FORMAT function. I understand.

P
Forum|alt.badge.img+6
  • petebocken
  • Author
  • New Participant
  • December 29, 2023

Update: I guess I can use the “month count” formula instead that works and returns zeros for every current month entry.

(DATETIME_FORMAT({📅 Date}, 'MM') - DATETIME_FORMAT(TODAY(), 'MM')) +((DATETIME_FORMAT({📅 Date}, 'YYYY') - DATETIME_FORMAT(TODAY(), 'YYYY')) * 12)

——————————————-
I’m reusing this formula on another table. However, this date field doesn’t have time enabled.

When I setup the formula it is not including the first entry of the month (Dec 1).

DATETIME_FORMAT(SET_TIMEZONE({📅 Date}, 'America/Chicago'), 'YYYY-MM') = DATETIME_FORMAT(NOW(), 'YYYY-MM')

Terms & ConditionsAccessibility statement