Help in matching a list of weekly periods to a range of changing time periods

[Alayna]

Hi, I have a pretty complicated workflow so I'm sorry if this isn't the best place for help. To give some background: Our payroll pays out bonuses for employees based on their earnings in a performance period. This bonus must be prorated based on the amount of days worked within that performance period, excluding weekends. So each work week would be 5 days.

I am trying to help them count those days worked for each employee, but the biggest obstacle is that because of different pay frequencies and inconsistent earnings, the actual earnings begin and end dates can't be used to prorate. We have another set of dates called FLSA dates - these are week by week begin and end dates that must be applied to different pay frequencies to better break down the work weeks

For example, let's say in a performance period of 4/1-4/30, an employee with a Mon to Sun pay frequency has these different earnings weeks:

3/29-4/4 - This is fine as it's a 7 day period with 5 work days. But the begin date is before 4/1, so he technically worked 2 days that week

4/5-4/20 - This is more than a 7 day period and must be broken up by 3 FLSA weeks: 4/5-4/11, 4/12-4/18, 4/19-4/25

4/21 - 4/27 - This is a 7 day period but it starts on Wed and ends on Thurs. Need to apply it to a Mon-Sun FLSA week, 4/19-4/25 and 4/26-5/2

In total, he worked 22 weekdays.

I know that's very complicated and I've struggled to make an efficient workflow, but I attached an example with notes and examples on my logic. There are 3 different pay frequencies: Weekly paychecks (easy to work with since already broken down in weeks), biweekly (mostly paid every two weeks), and semi-monthly (the hardest to account for, can be paid every 2 weeks, 3 weeks, etc.). Right now the workflow works well enough but still misses some weeks, esp for semi-monthly. If anyone has the time to just take a look and provide some feedback, especially where I'm breaking down the flsa weeks. I'd really appreciate it. Version 2021.2

[MatthewO]

Hello @Alayna :

Attached is an example of how you might approach this. Some assumptions were made about the data set and those are explained in the comments. It may be helpful to reference the DateTime Functions documentation as well. These were used to extract the DayOfWeek in the example. I hope this is helpful for you!

[Alayna]

Hi @MatthewO 

Thanks! this is a very great start and much simpler way to find the days worked. But I should have clarified that the FLSA weeks are actually required for payroll purposes, so I would still need to match those earnings back into a FLSA period like the beginning of my sample workflow. I'm wondering if I could just use your [Date] field and maybe iterate to see which FLSA week it fits into?

Thanks again!

[MatthewO]

In the sample workflow, I see that you are joining the FLSA weeks in the following way:

It looks like you could add these steps at the beginning of the workflow, or at the end. I've attached another version with the joins at the end.