Identify Consecutive Days Worked

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

Need Consecutive Work Days-Scrubbed.xlsx

Workflow

Date Time

Help

Time Series

Accepted answers

caltang

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

For Grace.yxzp

All comments

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

Tile tool Use.yxmd

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.

Need Consecutive Work Days-Scrubbed.xlsx

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

11/1/2022

2022-01-01

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

11/1/2022

2022-01-01

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

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.