Want to get involved? We're always looking for ideas and content for Weekly Challenges.
SUBMIT YOUR IDEAThe link to last week’s challenge (challenge #40) is HERE
This week’s exercise looks at using Alteryx to calculate the number of weekdays during each pay period. Employees get paid twice monthly so the number of weekend days within a period can vary.
Objective: For each month and pay period, calculate the # of weekdays that make up the pay period (i.e. exclude weekend days from the calculation).
Here's a solution:
Better Late Then Never!
How about a Multi-Field Formula of:
DateTimeParse(Regex_replace('0' + Replace([_CurrentField_], '/', '/0'), '^0*(\d\d)/0*(\d\d)/0*(\d{4})', '$3-$1-$2'),'%Y-%m-%d')
to convert the strings into dates, and then a fomula step to count the weekdays like:
1+((DateTimeDiff([P1 End],[P1 Start],"days")*5 - (ToNumber(DateTimeFormat([P1 Start],'%w'))-ToNumber(DateTimeFormat([P1 End],'%w')))*2) / 7) - IF DateTimeFormat([P1 End],'%w')='6' THEN 1 ELSE 0 ENDIF - IF DateTimeFormat([P1 Start],'%w')='0' THEN 1 ELSE 0 ENDIF
Both these formulas are based on posts from @jdunkerley79 the only change I made is to use DateTimeFormat([P1 End],'%w') to get the weekday number.
Hi @GeneR / @TaraM- the startFile that's posted for this challenge (#41) appears to be the solution file for challenge 40 (it has the input data and solution canvas for #40).
Would you mind checking if this is something on my end, or if the challenge files may have been mixed up a little?
Thank you
Sean
:-) did it a long way around (see below) - but very glad to see the super-efficient method from @Joe_Mako; and the solutions from @MattD & @brianprestidge
Also added a macro that makes it really easy to check for differences in column name; or value across the provided output data, and your own solution (attached)
My solution. Had never used Generate Rows before, so had to do a little digging, but eventually figured it out.
A different approach here. Separate into 2 streams for P1 and P2. Generate individual dates and then determine weekday of each. Filter out the weekends, and sum up to Month, Year for each Pay Period
Took me a while, and mine looks different, but it seems to work.
After changing to date formats, used Min/Max to find date range for each month to deal with different days in each month.
Used Multi-Row to add a row for each day in each month
Added formulas to identify week days (0=Weekend, 1=Weekday), then categorized the date into the P1 or P2 range
Summarized final results.