Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Sep 29, 2024 01:40 PM
Hi all
As per my screenshot below, I have a filter for orders with an ETD (estimated time departure) on or before the last day of the current month. On the first day of each month i currently manually update this.
How can I set up an automation that occurs on the first day of each month that updates 'exact date' to the last day of each month? e.g. 30th sepertember today so 1st October should update filter with exact date to 31st October.
@kuovonne @TheTimeSavingCo - let me know if you guys have any suggestions too please? Thank you!
Thank you
Sep 29, 2024 07:54 PM
Perhaps try using formula fields instead to filter off of? You'd have a formula field that outputs the last day of the current day's month, and another field that checks whether "Date Required" is before that:
DATEADD(
DATEADD(
DATETIME_PARSE(
DATETIME_FORMAT(
TODAY(),
"MM YYYY"
),
"MM YYYY"
),
1,
'month'
),
-1,
'day'
)
{Date Required} <= {Last day of this month}
Sep 29, 2024 08:08 PM
Thanks that'll work. Strange that there is no drop down field which would allow you to select 'End of Current Month' instead of having to specify what this exact date is
Sep 29, 2024 09:16 PM
You can use the condition that the date is within this calendar month to get records for the current month, including up to the end of the current month. To also get dates before the current month, use "or" with the condition that the date is before today.
In general, I recommend avoiding formula fields that use TODAY() because that formula can be resource intensive.
Sep 30, 2024 03:03 AM
My filter does not allow me to use 'Or'? it only allows 'And'?