Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Current Month formula grabbing last entry of last month

1110 0
cancel
Showing results for 
Search instead for 
Did you mean: 
petebocken
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

Screen Shot 2023-10-09 at 13.27.06.png

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 4
Sho
11 - Venus
11 - Venus

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.

petebocken
5 - Automation Enthusiast
5 - Automation Enthusiast

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')

 

 

Sho
11 - Venus
11 - Venus

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

petebocken
5 - Automation Enthusiast
5 - Automation Enthusiast

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')

IMG_7866.jpeg