Identify Consecutive Days Worked

[Gr4c3Sult]

Hello Alteryx Community!

I am working on automating the calculation of Regular Time (RT), Overtime (OT) and Double Time (DT) for a California time sheet with complex rules.  I have identified a few spots in the development plan I am going to need help with and I will tackle my first challenge here.

I need help identifying consecutive days worked (for now).

Rules:

Daily hours are up to 9 are RT

Daily hours over 9 but less than 12 are OT

Daily hours over 12 are DT

On the 5th or 6th consecutive days worked hours up to 8 are RT and over 8 are OT

On the 7th consecutive day worked hours up to 8 are OT and over 8 are DT

Saturday and Sundays hours up to 8 are OT and over 8 are DT

Holiday hours up to 8 are OT and over 8 are DT

Every other Friday is a scheduled day off and hours up to 8 are OT and over 8 are DT

After 40 RT hours, Daily hours up to 8 are OT and over 8 are DT

The work week begins and ends on Fridays.  However, the Fridays are split into 2 separate weeks of time.  For example, an employee’s 1st day of the week starts at 11am (others can have different start times) on Friday, 11-11-2022 and he worked from 8am to 12pm, clocked out and returned at 12:30pm to work until 5:30pm.  Any time before 11am was paid in the previous week.  Therefore, the previous week’s Friday RT, OT and DT rule will apply to the time associated with the 1st day of the week I am calculating pay and labeled accordingly as RT, OT and DT.  So, I need to identify if the previous week’s Friday is a 5th, 6th or 7th consecutive day (along with if it is a holiday or scheduled day off).  For this challenge, let’s just focus on the consecutive days  for the First Friday or as labeled in the sample data “Previous Week Friday and 1st Day” and the consecutive days for the week being calculated.

Day 1 (First Fridays of week being paid):

Week 2 always ends with a scheduled day off.  So the first Friday of Week 1 will always be up to 8 total hours OT and over 8 DT (the strictest of rules).  However, it can not be assumed that the first Friday of Week 2 will be RT because it is not a scheduled day off; It could be a consecutive day or a holiday.

To clarify a few questions you may have:

This will be an app published to our server with the following questions asked to identify key decisions for calculations such as:

First Friday Date

Last Friday Date

Week 1 or Week 2

Holiday 1 Date

Holiday 2 Date

If there are no holidays, a date outside of the date range of the 2 weeks of time uploaded must be selected.

(On an extremely rare event) if an employee works everyday Friday to Friday including weekends, there will be an 8th day.  If an 8th day is identified, I will treat it as an additional 7th day from a rules perspective.

Example: 

In the following example, I am calculating pay for Week 2.  The first day of the week will be 11/11/2022 with the 6th consecutive day Rule in place.  Notice that Sat is day 2, if Sun followed Fri instead then it would be day 1.  The count starts over if a day is skipped with no clocked time.

Name DAY DATE ACTUAL IN ACTUAL OUT Week Week Calculating Consecutive Day Employee A Fri 11/4/2022 05:00:00 09:21:00 Previous Week 2 1 Employee A Mon 11/7/2022 09:51:00 14:26:00 Previous Week 2 2 Employee A Tue 11/8/2022 10:01:00 14:31:00 Previous Week 2 3 Employee A Wed 11/9/2022 04:48:00 09:30:00 Previous Week 2 4 Employee A Wed 11/9/2022 10:02:00 14:30:00 Previous Week 2 4 Employee A Thu 11/10/2022 04:48:00 09:31:00 Previous Week 2 5 Employee A Thu 11/10/2022 10:02:00 14:30:00 Previous Week 2 5 Employee A Fri 11/11/2022 04:54:00 09:30:00 Previous Week Friday and 1st Day 2 6 Employee A Fri 11/11/2022 10:00:00 13:30:00 Previous Week Friday and 1st Day 2 6 Employee A Sat 11/12/2022 04:54:00 09:30:00 2 2 2 Employee A Sat 11/12/2022 10:00:00 13:30:00 2 2 2 Employee A Mon 11/14/2022 04:54:00 09:38:00 2 2 1 Employee A Mon 11/14/2022 10:08:00 14:31:00 2 2 1 Employee A Tue 11/15/2022 04:54:00 09:33:00 2 2 2 Employee A Tue 11/15/2022 10:03:00 15:30:00 2 2 2 Employee A Wed 11/16/2022 04:52:00 09:31:00 2 2 3 Employee A Wed 11/16/2022 10:01:00 15:30:00 2 2 3 Employee A Thu 11/17/2022 04:54:00 09:44:00 2 2 4 Employee A Thu 11/17/2022 10:15:00 15:35:00 2 2 4 Employee A Fri 11/18/2022 04:55:00 09:31:00 2 2 5 Employee A Fri 11/18/2022 10:01:00 13:35:00 2 2 5
I have attached a sample data set.  I have tried numerous things and have failed miserably.  Any help identifying the consecutive days worked is greatly appreciated!! Thank you in advance 😊 Grace

[Gr4c3Sult]

@caltang 

Yes, I can simplify this even further (attached).  I just need consecutive days worked identified, including holidays and weekends, I can work out the application of the rules.  The count only restarts on Friday's and if a day is not worked (skipped). If a Friday to Friday consecutive count is worked every single day it will be 8 consecutive days.  I am thinking the Generate Rows tool could be used to add the days missing (not worked) as zero hours and the Multi-formula tool could be used to build a formula that says something like ...if previous row is zero and current row does not equal zero then count is one if not than previous count plus one. The rub is that Friday is both the last and first day.  The last Friday day from the previous week's rules will apply to the first Friday of the week I am calculating.  So I need to know if the previous week's Friday is a consecutive day for that week and it counts as day ONE for the week I am calculating.  

Using this train of thought... one idea I had was to filter the "Previous Week" and "Previous Week Friday and 1st Day" out.  Then use the Generate Rows to add missing days not worked and use the Multi Row Formula to identify consecutive days using if then statement (s) Call this column "Consecutive Days".  Then Union that back with the Week 2 data to filter "Previous Week Friday and 1st Day" with "2" again using Generate Rows and Multi Row Formula to identify consecutive days in a different column called "Consecutive Days-2",  Then filter Out "2" replace "Consecutive Days" with data from "Consecutive Days-2" using Formula tool. Then Union the True (" Previous Week Friday and 1st Day") with the False "2".  Then Select tool to remove "Consecutive Days-2" column.  SOOO really, if this idea jives.. all I need help with is the Generate Row formula to get missing days not worked and the Multi-Row Formula to count the days actually worked.

Sigh... this is so painful.

Truly appreciate your help!

Grace

[caltang · Accepted solution]

Hi @Gr4c3Sult 

I think I cracked it! Though my way of doing it is to go Employee by Employee to ensure it's correct. 

Please let me know if this works. 

Best regards,

Cal

[Gr4c3Sult]

WOO WHOO! That gets me something to work with! I think I can use the Record ID tool to apply the workflow to all the employees :) THANK YOU!!!!  Grace

[caltang]

My pleasure! Thanks for the likes & marked as solution.