Convert date to a business day

[ryan_b]

Hi everyone,

New to Alteryx, wanted to reach out to see if there was a way to convert a date into a business day during the month.

For example:

Date	Business day
6/1/2018	1
6/5/2018	3
7/2/2017	1

Thanks!

[david_fetters]

You will have to convert your [Date] field to a Date or Datetime type first using the DateTimeParse() function in a formula tool.  See the manual on date time functions.  The parse function uses a date time format string to convert a date stored as text into an actual date that can be used in other date/time functions.  For something like "6/1/2018" (American format, e.g. June 1, 2018), the function would be:

DateTimeParse([Date],"%m/%d/%Y")

Once it is in a Date or Datetime format, you can output the day of the week as 'Tue' by using the function:

DateTimeFormat([Date], "a%")

If you want a full day name, e.g. 'Tuesday', then replace a% with A%.

From that point, you can use a Switch() statement to return what business day (with a default of 0 if saturday or sunday) using the below function:

Switch(DateTimeFormat([Date], "a%"),0,
"Mon",1,
"Tue",2,
"Wed",3,
"Thu",4,
"Fri",5) 

If you want, you can do the entire process in a single function.  We just need to wrap all the steps into single nested function: parsing a string into a datetime, returning the day of the week, and converting the day into a numerical business day.  For excel ninjas, this should feel familiar:

Switch(DateTimeFormat(DateTimeParse([Date],"%m/%d/%Y"), "a%"),0,
"Mon",1,
"Tue",2,
"Wed",3,
"Thu",4,
"Fri",5)

[Greg_Murray]

Hi Ryan, 

See attached. I think its what you're looking for. It weeds out Saturdays, Sundays and (optionally) holidays, then calculates the business day of the month for a particular day. With a little more work you could turn this into a macro. 

Hope that helps. 

Greg 

[ryan_b]

Thanks Greg, that was really useful and answered most of my questions.

One thing that did come up is that, is there a way to group Saturday and Sunday into the next business day, for example the following Monday?

Example: 6/2/2018 or 6/3/2018 will return a business day of 2

[Greg_Murray]

Hi Ryan,

Definitely possible. How would you want to handle it if the next closest business day is in the following month? Should it be included in the next month? (i.e. march 30 & 31 are sat and sun - Should they be business day 1 for april?)

Greg

[ryan_b]

Hi Greg,

In the cases where the next closest business day is in the following month, would it be possible to create an exception to group it to the day prior? (i.e. March 30 & 31 would be the last business day in March, so business day 22 - March 29)

Thanks for the help!

Ryan

[Greg_Murray · Accepted solution]

Okay I think this macro does what you are looking for, but I would do some testing before you use it in anything. 

There are two inputs

D - Data Stream with date field

H - Optional Holidays table

There are two outputs

M - Matches/Data stream output.

N - non matches - will only return data if the "include saturday and sunday in nearest business day in the same month" checkbox is NOT selected.

Note: if the month splits between saturday and sunday  (i.e. sat is 7/31 and sun is 8/1) then Saturday will be included in the nearest business day of the previous month and Sunday will be include in day 1 of the next month. 

Let me know if you have questions. 

Greg

[ryan_b]

Hi Greg,

Thanks for providing this it was much appreciated, I was able to test it and it mostly works!

One thing I saw when testing the macro was that when I made one of the D inputs as a holiday, it actually outputs to N

I added a connection from the J in the Join tool to the Union tool and it fixed the issue.

Thanks,

Ryan

[JFAU]

THIS IS AWESOME!