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

[caltang]

Hi @Gr4c3Sult,

Just to clarify, the week starts on a Friday, and ends the following Friday right?

Also, Public Holidays for the USA + California right? (Sorry, I'm not American). Some what-ifs, let's say the Governor declares a special holiday (is that possible?) - how will this be treated?

This is a really interesting challenge, one worthy to be a weekly challenge!

[Gr4c3Sult]

Hi @caltang ! 

Yes, the week starts on a Friday at a certain time of the day (different times for different employees)  and ends the following Friday at the same time it began the previous Friday.  The holidays can be any day the person running the app declares when answering the questions the app will pose (via interactive tools).  I have a portion of the overall workflow created and stuck right now with identifying consecutive days worked.. that is the only part I am currently asking help on :)

I am not going to lie, my brain hurt just wrapping my head around the rules and all the possible scenarios and problems that could happen... and then mapping out a development plan! 

Appreciate your help tackling this!!

Grace

[caltang]

Hi @Gr4c3Sult,

Great! Maybe you can export your workflow here for us to see and help you better? 

Let me think of something for identifying consecutive days worked.

[caltang]

Hi @Gr4c3Sult 

Try using the Tile Tool:

It gives you the Tile_Num which can then be renamed to Consecutive Days. Just have to work out the days!

[Gr4c3Sult]

@caltang 

That did not work.  I have attached the application of the tile tool configured as suggested.  I think the solution may require the use of the Generate Rows and Multi-row Formula.. 

Thanks!

Grace

[caltang]

Hmm, my apologies then. I think I'm still not understanding the full extent of your request properly. 

Maybe if you can provide an expected output, that would be great as well! Unless that expected output is what you posted above...

Because the Tile_Num:

Shows you the days of which the person works. If the counter # resets, that's for a new person already. 

In addition, I'm not sure why Holiday 1 and 2 are static figures... are there any reasons to it?

[caltang]

I see that your example above, the # of consecutive days takes into account the date, the time of which the employee starts, and the employeeID itself. 

Perhaps you can use the Sort Tool on the EmployeeID, the Date, and the Start Time? Then put your Tile tool, that should get you the outcome...

[Gr4c3Sult]

@caltang 

It is a bit more complicated than that.  I have added a column with the expected results added to the attached. Each employee can have a reset of the consecutive day worked count if a day is not worked.  

The holidays are static because I am only dealing with 2 weeks of time and there will never be more than 2 holidays in that given time frame, if any.  It also makes the workflow adjustable to Canadian timesheets.  However, the holiday really isn't a consideration for what I am asking for in this solution.  I just need help with identifying the consecutive days worked.  I shared the scope of the whole project to give insight on why.. I probably shouldn't have shared that much information.  I may simplify my post to be more direct, my apologies for any confusion the extra info may have caused.

[caltang]

No need for apologies! I'll be honest, I'm quite stumped on this problem myself. 

Reason being is the reset of the cumulative days happening for employees if it hits a weekend or a Public Holiday. We also have to take into account the number of cumulative hours worked on a single day, if it exceeds 8 then it's counted as DT too. 

Is it acceptable to simplify the dataset further? So that users who worked on the same date, but at different times (clock in and out), are merged into 1 row? 

For example:

Employee A

M1763297

BH30995816V00

Fri

11/11/2022

002-9AM CENTURION

04:54:00

09:30:00

4.6

Previous Week Friday and 1st Day

2

11/1/2022

2022-01-01

6

Employee A

M1763297

BH30995816V00

Fri

11/11/2022

002-9AM CENTURION

10:00:00

13:30:00

3.5

Previous Week Friday and 1st Day

2

11/1/2022

2022-01-01

6

Employee A on the 11th of November 2022 clocked in different times but at the same date. The cumulative hours in this case > 8 hours, and if we can simplify it into 1 record, that would be easier to process I believe. Just checking with you first before I proceed to test further.

-Cal