Dynamic Date Formula in a Filter tool
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello Experts,
I need your help.
I created three dynamic formulas to filter data based on the criteria below. I am experiencing basically the same error in each permutation; the filter is not listing the first day of the date range that is evaluated.
Formula 1: If Test Date is between 1- 15 day of the month, then it will filter the data from dates between 1-15 prior month, otherwise, it will filter from dates 16- to end of the prior month. Error: Using Test date as 2024-03-20, The filtered list start from 2024-02-17 instead of 2024-02-16, but date 2024-02-16 is in database.
Formula 2: If Test Date is between the 28-31 day of the month, then it will filter the data of current month, otherwise it will filter data for previous month. Error: Error: Using Test date as 2024-03-20 The filtered list starts from 2024-02-02 instead of 2024-02-01, but date 2024-02-01 is in data base.
Formula 3: if Test Date is between 1- 15 day of the month, then it will filter the data from 16 to end day of prior month, otherwise, it will filter from 1- 15 day of same the month. Error: Using Test date as 2024-03-20, it did not display date 2024-03-01 but report starts from date 2024-03-02 (date 2024-03-01 is in data base).
Can someone please review and help me find out why it is not working as expected?
The data based contains all 2004 dates and up 02/06/2025 for further testing.
Thank you!
Solved! Go to Solution.
- Labels:
- Date Time
- Expression
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@grromerro The DateTimeTrim() function returns datetime data type , the EFFECTIVE_DATE field is Date type, that is why the difference, use the toDate() function to convert DateTime to Date data type
IF DateTimeDay([TEST DATE]) <= 15 AND DateTimeDay([TEST DATE]) >= 1 THEN
([EFFECTIVE_DATE] >= toDate(DateTimeTrim(DateTimeAdd([TEST DATE], -1, 'months'), 'FirstOFMonth'))
AND
[EFFECTIVE_DATE] <= toDate(DateTimeAdd(DateTimeTrim(DateTimeAdd([TEST DATE], -1, 'months'), 'FirstOFMonth'), 14, 'days')))
ELSE
([EFFECTIVE_DATE] >= toDate(DateTimeAdd(DateTimeTrim(DateTimeAdd([TEST DATE], -1, 'months'), 'FirstOFMonth'), 15, 'days'))
AND
[EFFECTIVE_DATE] <= toDate(DateTimeAdd(DateTimeTrim(DateTimeAdd([TEST DATE], 0, 'months'), 'EndOFMonth'), -1, 'days')))
ENDIF
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you for taking the time to review. When I use the proposed formula with toDate() function, the workflow does not filter data using test date 2024-03-20. What else could I try? Thank you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@binuacs Thank you for taking the time to review. When I use the proposed formula with toDate() function, the workflow does not filter data using test date 2024-03-20. What else could I try? Thank you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@grromerro i updated your formula with toDate() and i got the result
IF DateTimeDay([TEST DATE]) <= 15
THEN [EFFECTIVE_DATE] >= toDate(DateTimeTrim(DateTimeAdd([TEST DATE], -1, 'months'), 'month') ) AND [EFFECTIVE_DATE] <= toDate(DateTimeAdd(DateTimeTrim(DateTimeAdd([TEST DATE], -1, 'months'), 'month'), 14, 'days'))ELSE
[EFFECTIVE_DATE] >= toDate(DateTimeAdd(DateTimeTrim(DateTimeAdd([TEST DATE], -1, 'months'), 'month'), 15, 'days'))
AND [EFFECTIVE_DATE] <= toDate(DateTimeAdd(DateTimeTrim(DateTimeAdd([TEST DATE], 0, 'months'), 'month'), -1, 'days'))
ENDIF
