I have a table that contains subscription dates using the date field. I’m trying to create a new grid view with a filter that just shows dates that fall into the current month. Seems simple, but I can’t figure it out. I can see how I can use the “is on or after” and “is on or before” operators, but I’d have to manually enter the dates each month.
Am I overlooking a setting or is my ask more complicated than I assume?
Thanks for the tip. I had seen something along those lines in a post from a few years back and was hoping there was some sort of update. That said, this will work for now.
Actually the code above can show dates from past years since it compares only the month numbers (1 for January 2021, it is also 1 for January 2022). So, the view will also show records from past years.
This formula checks also for years. Returns 1 if the date is this month, returns 0 if it is not. You can create a filter condition, “Where {this formula field} = 0”=
One of those TODAY() functions should actually be the date field to compare against. Comparing today against today isn’t going to help much. :slightly_smiling_face:
Another point to note is that the date returned by TODAY() is going to shift at some point in the middle of your day unless you happen to live in the GMT timezone. That’s because TODAY() returns the current date at GMT. When the date changes at GMT, so does the output of TODAY(), no matter where in the world you live.
Using NOW() is going to be a little more accurate because that also takes the current time into account, but like TODAY(), the output of NOW() is also based on GMT.
To adjust the output to be based on your local time, you’ll need to also use the SET_TIMEZONE() function wrapped around the date. I’m using my local timezone in the example below; replace that with the appropriate timezone specifier for your region from this page:
Finally, the output of an equality comparison already returns 1 for True and 0 for False, so the wrapper IF() function is not necessary.
With all of that in mind, here’s a modified formula to try:
If your subscription date field is only a date and doesn’t contain a time, then drop the SET_TIMEZONE() function in the second DATETIME_FORMAT(). Dates with no time automatically default to midnight internally, and shifting them based on the timezone actually makes the comparison less accurate.
Thanks for your attention. Of course one of the today() functions should be replaced by the date field. Again thanks for explaining the difference between today and now functions. I did not know it.
Make the first line: Where | Date | is on or after | the first day of the month
Make the second line: Where | Date | is on or before | the last day of the month
This will return the results of days between the two dates.
