Remove weekends

[KLS]

Hi,

Have a scenario where I would like to include previous day. Ive added in the formula as DateTimeAdd([Date],1,"days") =datetimetrim(datetimestart(),"day"). However this day actually falls on a Sunday as Im running them on Monday(today). Is there a way to exclude Sat/Sun and include in last week latest date ie Friday?

20th April( today) need to include in 17th April (last Friday).Appreciate the help thank you.

[RolandSchubert]

Hi @KLS ,

you can use the formula:

IF DateTimeFormat([Field1], '%u') != '1' THEN
DateTimeAdd([Field1], -1, 'days')
ELSE
DateTimeAdd([Field1], -3, 'days')
ENDIF

the DateTimeFormat condition checks, if the day is Monday and DateTimeAdd subtracts 3 days if this is true, otherwise only one day is subtracted.

Let me know if this works for you.

Best,

Roland

[KLS]

Hi @RolandSchubert  ,

Thanks but seems the  formula logic is not  captured (below) as it captures both dates still. Is there an additional step(s) required?

[RolandSchubert]

i @KLS ,

I assumed, the formula only has to consider Monday - Friday ... I extended the formula a bit (usually previous day, for Monday and Sunday back to Friday).

IF DateTimeFormat([Field1], '%u') = '1' THEN
DateTimeAdd([Field1], -3, 'days')
ELSEIF DateTimeFormat([Field1], '%u') = '7' THEN
DateTimeAdd([Field1], -2, 'days')
ELSE
DateTimeAdd([Field1], -1, 'days')
ENDIF

Works now as expected?

Best,

Roland 

[KLS]

@RolandSchubert  I get what your trying to do but alteyrx somehow not able to capture the logic.weird as it still captures both dates.

 My output

[KLS]

@RolandSchubert have attached workflow. Appreciate the help please, thank you

[RolandSchubert]

Using that formula, I create an additional field ([Previous Day] - is that what you want to achieve?

In your solution, I think you should look at the field [test]. 

[RolandSchubert]

Would you provide the data file as well?

[KLS]

hi @RolandSchubert 

Maybe I should rephrase my question. If I were to use DateTimeAdd([Date],1,"days") =datetimetrim(datetimestart(),"day") on a Monday, the above formula wont work as it will capture -1 minus day (SundaY). Is there a logic I can incorporate to include logic that capture date on previous business day (ie Friday) if i run them on Monday especially.

Have tried Max date Summary tool. It wont work as date in file might not be the latest. Im trying to picture a scenario if there are trades that falls on a Friday hence I am able to capture them on Monday. Hope this helps, thank you again.

[RolandSchubert · Accepted solution]

Hi @KLS ,

looks like I got it wrong in my first approach - you want to select only the records dated of last workday.  I replced the Formula tool by a Filter tool now and turned the formula into a condition. What do you think?

Best,

Roland