weekday

[DennyChan]

Hello

I have a date that i need to changed to business - 1 every time i run a Alteryx work flow , I was wondering if there is a way to test the day IE Monday and return a Friday date 25 jan 2016 will be replaced by 22 jan 2016

Many thanks

Denny

[RodL]

There could be a few different ways to do this, but a simple one that works for your example is a Formula tool with the expression:

IF DateTimeFormat([Date],'%a') = 'Mon'
THEN DateTimeAdd([Date],-3,'days')
ELSE DateTimeAdd([Date],-1,'days')
ENDIF

The first line converts the date to an abbreviated day of the week and tests if Monday.

Depending on the results, it then adjusts the date by 1 or 3 days.

This assumes the date field is in a date format. For your example format, you would need to convert to a date format before doing the above.

Thanks for the question,

Rod

[jdunkerley79 · Accepted solution]

Simplest is to use DateTimeFormat to get day of week and then use DateTimeAdd:

DateTimeAdd([Date],IIF(DateTimeFormat([Date],"%a")="Mon",-3,-1),"days")

If you want to be safer then use a switch to cope with Sundays:

DateTimeAdd([Date],
            Switch(DateTimeFormat([Date],"%a"),-1,
                                         "Mon",-3,
                                         "Sun",-2),
            "days")

 Demo workflow of later formula attached.

[DennyChan]

many thanks on your help

Denny