Counting days in a list of weeks within a larger date range - ignoring weekends

[Alayna]

Hi, Alteryx

I have a workflow that takes many different earnings weeks (called flsa weeks) and tries to prorate a bonus for each week that an employee works. This is based on a performance period range, so I'm trying to first count the total number of  work days in the performance period and divide the bonus by those days. 

The issue is that the bonus is only given for days in a "work week" -> Monday - Friday. So I have to make sure Alteryx is ignoring Saturdays and Sundays within each flsa week. Another issue is that flsa weeks do not follow the same pattern - it can be from Mon-Tues, Wed-Thurs, Sat-Sun, etc. So I also need the workflow to count the correct days if the flsa week begins before or ends after the performance period.

Let's say the performance period is from 5/1/2021-5/31/2021. The flsa weeks could look like this below:

2021-04-29 | 2021-05-05 
2021-05-06 | 2021-05-12 
2021-05-13 | 2021-05-19 
2021-05-20 | 2021-05-26 
2021-05-27 | 2021-06-02 

The first week should have 3 work days (5/3-5/5 only apply), the next three weeks should have 5 days each, and the last week should have 3 days (5/27, 5/28, and 5/31 only apply). I have an attached workflow I'm working on, but I'm having some trouble thinking of an efficient way to do this without having multiple formulas, especially since I feel like I'm hardcoding too much at this point. The proration piece has given us many many issues while testing. Any help is appreciated

[Luke_C · Accepted solution]

Hi @Alayna 

Here's my take:

1. Used generate rows to create a record for every day in the period (5/1-5/31)
2. Added the day of the week for each day
3. Filtered to remove weekends, and also just records between the FLSA date ranges
4. Summarized to count days in the FLSA range
5. Continued your existing logic.

This removes the need for a lot of the formulas you were doing.

[Alayna]

@Luke_C This is genius and much more simple than what I was doing. Thank you! I always forget about generate rows