This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
The 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).
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.