Help

Re: Current Month formula grabbing last entry of last month

1348 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