Date time today - 1 without weekends?

[Joker_Hazard]

Hello guys!

I need to create a column with datetimetoday -1 (one day) and this formula should only consider monday to friday.

The workflow has no historical data, so its reading an SQL for the first time. 

An example:

Any tips? Thanks!

[JosephSerpis · Accepted solution]

Hi @Joker_Hazard I mocked up an approach let me know what you think? For reference the specifiers I used in the datetime functions can be found here.

 

[Joker_Hazard]

Very Clever solution my friend! Thanks!!

[Cal_A · Accepted solution]

If the input dates are all weekdays (Mon-Fri) then you can probably do in one formula step. Basically the structure would be:

IF [INPUT DATE] = Monday THEN

    [INPUT DATE] - 3 day

ELSE

    [INPUT DATE] -1 day

In alteryx code the only tricky bit is determining whether it is Monday.  You can do this with

IF datetimeformat([INPUT DATE],"%w") = 1 THEN

datetimeadd([INPUT DATE],-3,"day")

ELSE datetimeadd([INPUT DATE],-1,"day")

ENDIF

If you have to handle Mondays, Sundays and Saturdays then you would need to say IF Monday then -3, IF Sunday then -2, ELSE -1 day.

nb this avoids using abacus due to problems some people have with that. https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Getting-Day-Of-Week-DOW-in-number-form...