Subtract Business Days from Date

[wonka1234]

Hi,

Say I have month end dates in one column

ie 2023-07-31 .

How can I get 5 business dates before this date? Ie 2023-07-25

[suby]

Hi,

Try using this formula.

DateTimeAdd([Date],-5,'days')

Thanks

[TheMattLeonard]

As far as I know Alteryx doesn't have a built in Time Date Formula to address Business Days. I think I have a solution that should work for your use case though.

1. You'll need your column of Month End Dates in the Alteryx Date Data Type (YYY-MM-DD) 

2. Use a Date Time Tool. You're going to select "Date/Time Format to string", select your Month End Field, and then select "day, dd Month, yyyy" as the format for the DateTime_Out column.

3. Use a formula tool. Create a New Field called "Less 5 Business Days" or whatever you'd like. Use this formula:

IF Contains([DateTime_Out], "Monday")
THEN DateTimeAdd([Date],-6,"Days")
ELSEIF Contains([DateTime_Out], "Tuesday")
THEN DateTimeAdd([Date],-6,"Days")
ELSEIF Contains([DateTime_Out], "Wednesday")
THEN DateTimeAdd([Date],-6,"Days")
ELSEIF Contains([DateTime_Out], "Thursday")
THEN DateTimeAdd([Date],-6,"Days")
ELSEIF Contains([DateTime_Out], "Friday")
THEN DateTimeAdd([Date],-4,"Days")
ELSEIF Contains([DateTime_Out], "Saturday")
THEN DateTimeAdd([Date],-5,"Days")
ELSEIF Contains([DateTime_Out], "Sunday")
THEN DateTimeAdd([Date],-6,"Days")
ELSE Null()
ENDIF

This formula basically just checks what day of the week the month end is on and then goes back the appropriate amount of days depending on what it is. You will need to change the name of your Month End column to be [Date] or you can update the formula to match your Field Name.

Let me know if this works for you.

Thanks

[wonka1234]

@TheMattLeonard  thanks looks to be working although the count seems off...

my month will be 2023-07-31 and your result gives me 2023-07-17! that seems more then 5 business days!

[jdminton · Accepted solution]

@wonka1234 This workflow will do that. I used the day of the week to determine how many to subtract.

 

 

 

[caltang]

There is a discussion here: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Counting-number-of-business-da... 

That has many workflows provided to match your requirements.

Also, you didn't mention about public holidays - do you still count them or no? @wonka1234