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:
Thanks!
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
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)
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.
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
