Weekly Challenge

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語

Want to get involved? We're always looking for ideas and content for Weekly Challenges.


Challenge #41: Analytics PayPeriodCalc

Alteryx Alumni (Retired)

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).

Alteryx Alumni (Retired)

Here's a solution:

41 solution.PNG
Former Alteryx, Inc. Support Engineer, Community Data Architect, Data Scientist then Data Engineer
8 - Asteroid

Better Late Then Never! 

My Solution:
Screen Shot 2016-09-26 at 19.36.49.png
12 - Quasar

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.


count weekdays.png

17 - Castor
17 - Castor

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


17 - Castor
17 - Castor

:-) 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


- Appended a row to this set for every day of the month with a unique number between 1 & 31 - so each row in the original set now has 31 rows
- then converted this to a test date in the same month
- added a weekday flag to this test date
- then counted the number of rows where it was a weekday and either fell in the P1 range or the P2 range (simple summarize)

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)


ACE Emeritus
ACE Emeritus

My solution. Had never used Generate Rows before, so had to do a little digging, but eventually figured it out.


15 - Aurora
15 - Aurora

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

15 - Aurora



Weekly Challenge 41.png
8 - Asteroid

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.