Filter IF statement to return three days if today is monday?

I have a workflow that Tuesday through Friday I need to pull the previous day's data from a database, but on Monday I need to pull three day's worth (Friday, Saturday, and Sunday).

Is there something I can add to the below IF statement to also return Saturday and Sunday? or am I taking the wrong approach?

As is, it is giving me Friday's data if today is Monday, and just prior day for the rest of the week(so mostly working).

IF DateTimeFormat(DateTimeToday(), "%a") = "mon"
THEN DateTimeFormat([SCRIPT_RUN_DATE],"%Y-%m-%d") = DateTimeFormat(DateTimeAdd(DateTimeToday(), -3, "days"), "%Y-%m-%d")
ELSE DateTimeFormat([SCRIPT_RUN_DATE],"%Y-%m-%d") = DateTimeFormat(DateTimeAdd(DateTimeToday(), -1, "days"), "%Y-%m-%d")
ENDIF

Thanks

Date Time

Accepted answers

patrick_digan

@Mike-T Here is the formula I would use:

DateTimeDiff(DateTimeToday(),DateTimeTrim([SCRIPT_RUN_DATE],"days"),"days") <(IIF( DateTimeFormat(DateTimeToday(), "%a") = "mon",4,2))

That will take the difference between today and script run date (in whole days) and then grab either the previous day (<2) or the previous three days (<4) depending on the day of the week. I've taken the liberty of completely changing around your formula. If you wanted something closer to what you currently have, you could just change the then statement as shown below to use the datetime diff:

IF DateTimeFormat(DateTimeToday(), "%a") = "mon"
THEN DateTimeDiff(DateTimeFormat(DateTimeToday(), "%Y-%m-%d"),DateTimeFormat([SCRIPT_RUN_DATE],"%Y-%m-%d"),"days")<4
ELSE DateTimeFormat([SCRIPT_RUN_DATE],"%Y-%m-%d") = DateTimeFormat(DateTimeAdd(DateTimeToday(), -1, "days"), "%Y-%m-%d")
ENDIF

All comments

patrick_digan

@Mike-T Here is the formula I would use:

DateTimeDiff(DateTimeToday(),DateTimeTrim([SCRIPT_RUN_DATE],"days"),"days") <(IIF( DateTimeFormat(DateTimeToday(), "%a") = "mon",4,2))

IF DateTimeFormat(DateTimeToday(), "%a") = "mon"
THEN DateTimeDiff(DateTimeFormat(DateTimeToday(), "%Y-%m-%d"),DateTimeFormat([SCRIPT_RUN_DATE],"%Y-%m-%d"),"days")<4
ELSE DateTimeFormat([SCRIPT_RUN_DATE],"%Y-%m-%d") = DateTimeFormat(DateTimeAdd(DateTimeToday(), -1, "days"), "%Y-%m-%d")
ENDIF

Rohit_Bajaj

Hi Mike,

Can you try creating a formula field Monday = IF DateTimeFormat(DateTimeToday(), "%a") = "mon" then 'Y' else 'N'?

In filter check if

(Monday = Y AND

DateTimeFormat([SCRIPT_RUN_DATE],"%Y-%m-%d") >= DateTimeFormat(DateTimeAdd(DateTimeToday(), -3, "days"), "%Y-%m-%d") && DateTimeFormat([SCRIPT_RUN_DATE],"%Y-%m-%d") <= DateTimeFormat(DateTimeAdd(DateTimeToday(), -1, "days"), "%Y-%m-%d") OR

(MONDAY=N AND

DateTimeFormat([SCRIPT_RUN_DATE],"%Y-%m-%d") = DateTimeFormat(DateTimeAdd(DateTimeToday(), -1, "days"), "%Y-%m-%d"))

Please see if this can get you started.

Thanks,

Rohit Bajaj

Mike-T

@patrick_digan wrote:
@Mike-T Here is the formula I would use:
DateTimeDiff(DateTimeToday(),DateTimeTrim([SCRIPT_RUN_DATE],"days"),"days") <(IIF( DateTimeFormat(DateTimeToday(), "%a") = "mon",4,2))
That will take the difference between today and script run date (in whole days) and then grab either the previous day (<2) or the previous three days (<4) depending on the day of the week. I've taken the liberty of completely changing around your formula. If you wanted something closer to what you currently have, you could just change the then statement as shown below to use the datetime diff:
IF DateTimeFormat(DateTimeToday(), "%a") = "mon"
THEN DateTimeDiff(DateTimeFormat(DateTimeToday(), "%Y-%m-%d"),DateTimeFormat([SCRIPT_RUN_DATE],"%Y-%m-%d"),"days")<4
ELSE DateTimeFormat([SCRIPT_RUN_DATE],"%Y-%m-%d") = DateTimeFormat(DateTimeAdd(DateTimeToday(), -1, "days"), "%Y-%m-%d")
ENDIF

patrick_digan
Thank you I think this is getting me on the right track, the only problem I am having is it is returning today's date along with the last 3 (I changed "mon" to "tue" for testing purposes. but I think I can figure this out using the DateTimeDiff function.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703