Dynamic Date Formula in a Filter tool

[grromerro]

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! 

[binuacs]

@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

[grromerro]

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!

[grromerro]

@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!

[binuacs · Accepted solution]

@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