Time Calculation Based on Shift Pattern

[Koza84]

Hello,

I try to find formula in column C to calculate time based on shift patterns that could be different. Sometimes shifts can be 8 hours per day, or it can be day off in the middle of the week, because of bank holiday.

Please see below data.

in column C are results.
C2= 13.5 hours, because 7.5 h was worked Monday and 6 hours Tuesday, based on shift times in column E:F
C3= 3.5 hours, because 3.5 h was worked Tuesday , based on shift times in column E:F
C4= 1 hour, because 0.5 h was worked Tue and 0.5 hours Wed, based on shift times in column E:F
C5= 8 hours, because 7.5 h was worked Wed, 0 hours Thursday as it was day off (that's why i have made start/end time on that date same) and 0.5 hour Fri, based on shift times in column E:F
C6= 13.25 hours, because 7.5 h was worked Fri and 5.75 hours Sat, based on shift times in column E:F

Any help is greatly appreciated.

Best regards

A B C E F

Start Time	End Time	Hours		Shift Start	Shift End
Mon 10-Dec-18 08:30	Tue 11-Dec-18 12:00	13.5		Mon 10-Dec-18 08:00	Mon 10-Dec-18 16:00
Tue 11-Dec-18 12:00	Tue 11-Dec-18 15:30	3.5		Tue 11-Dec-18 06:00	Tue 11-Dec-18 16:00
Tue 11-Dec-18 15:30	Wed 12-Dec-18 08:30	1		Wed 12-Dec-18 08:00	Wed 12-Dec-18 16:00
Wed 12-Dec-18 08:30	Fri 14-Dec-18 06:30	8		Thu 13-Dec-18 06:00	Thu 13-Dec-18 06:00
Fri 14-Dec-18 06:30	Sat 15-Dec-18 11:45	13.25		Fri 14-Dec-18 06:00	Fri 14-Dec-18 16:00
				Sat 15-Dec-18 06:00	Sat 15-Dec-18 12:00
				Sun 16-Dec-18 06:00	Sun 16-Dec-18 06:00

<tbody>
</tbody>

 

[Koza84]

I cannot help you with the layout you presented because I think it's confusing. I mean, let's look at this shift pattern:

Mon 10-Dec-18 08:30 Tue 11-Dec-18 12:00

In fact these are 2 shifts:

Mon 10-Dec-18 08:30 till Mon 10-Dec-18 16:00
Tue 11-Dec-18 06:00 till Tue 11-Dec-18 12:00

However, I can help you with another layout. When you are stuck with a fixed layout you probably get from work then you can stop reading.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr][tr][td]

1​

[/td][td="bgcolor:#0070C0"]Day[/td][td="bgcolor:#0070C0"]Date[/td][td="bgcolor:#0070C0"]StartTime[/td][td="bgcolor:#0070C0"]EndTime[/td][td="bgcolor:#0070C0"]Hours[/td][td][/td][td="bgcolor:#0070C0"]

ShiftStart​

[/td][td="bgcolor:#0070C0"]

ShiftEnd​

[/td][/tr]
[tr][td]

2​

[/td][td]Monday[/td][td]

10-12-2018​

[/td][td]

8:30​

[/td][td]

16:00​

[/td][td]

7,50​

[/td][td][/td][td="bgcolor:#FFFF00"]

6:00​

[/td][td="bgcolor:#FFFF00"]

16:00​

[/td][/tr]
[tr][td]

3​

[/td][td]Tuesday[/td][td]

11-12-2018​

[/td][td]

6:00​

[/td][td]

12:00​

[/td][td]

6,00​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td]Tuesday[/td][td]

11-12-2018​

[/td][td]

12:00​

[/td][td]

15:30​

[/td][td]

3,50​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]Tuesday[/td][td]

11-12-2018​

[/td][td]

15:30​

[/td][td]

16:00​

[/td][td]

0,50​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td]Wednesday[/td][td]

12-12-2018​

[/td][td]

8:00​

[/td][td]

8:30​

[/td][td]

0,50​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td]Wednesday[/td][td]

12-12-2018​

[/td][td]

8:30​

[/td][td]

16:00​

[/td][td]

7,50​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]Friday[/td][td]

14-12-2018​

[/td][td]

6:00​

[/td][td]

6:30​

[/td][td]

0,50​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]Friday[/td][td]

14-12-2018​

[/td][td]

6:30​

[/td][td]

16:00​

[/td][td]

9,50​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td]Saturday[/td][td]

15-12-2018​

[/td][td]

6:00​

[/td][td]

11:45​

[/td][td]

5,75​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in E2:

=IF(AND(WEEKDAY(B2;2)<>4;WEEKDAY(B2;2)<>7);IF(SUM(MAX(($H$2-$G$2-MAX(0;C2-$G$2)-MAX(0;$H$2-D2))*24;0);MAX(($P$4-$O$4-MAX(0;C2-$O$4)-MAX(0;$P$4-D2))*24;0))=0;"";SUM(MAX(($H$2-$G$2-MAX(0;C2-$G$2)-MAX(0;$H$2-D2))*24;0);MAX(($P$4-$O$4-MAX(0;C2-$O$4)-MAX(0;$P$4-D2))*24;0)));"")

And drag down. (Replace semi-colon with comma if you are USA).

The formula first looks at the weekday. If it's NOT a Thursday or Sunday then continue (because these are your days off).
The Weekday has 2 parameters: serial_number (which is the date) and return_type (which is a number and refers to start of week). I chose to start at Monday which is represented by yhe number 2.

Hi There.

Thanks for tour help. I cannot change layout column A and B are extracted from scheduling tool and it has to be one row per job, do I cannot split it like this. Appreciate your suggestion.

Best regards

 

[JumboCactuar]

Your first example, how do you get 13.5?
Should it not be 11.5

Monday 08:30 - 16:00 = 7.5 hours
Tuesday 08:00 - 12:00 = 4 hours
11.5

 

[Koza84]

Your first example, how do you get 13.5?
Should it not be 11.5

Monday 08:30 - 16:00 = 7.5 hours
Tuesday 08:00 - 12:00 = 4 hours
11.5

It should be 13.5, because 7.5 h worked Monday plus 6 hours Tuesday, because Tuesday 11 of Dec shift starts 6 am

Best regards

 

[Koza84]

Hello,

Still didn't find solution.

Let me please recapture issue here as I am not sure If I have explained this well enough.

I would like to calculate Run Time in column D. Run Time should include only business hours which are in columns F:G.

For example results explained as below:

For Product A run time is 9.5 hours, because product was manufactured 3.5 hrs Mon 10/12/18 and 6 hours Tue 11/10/18
For Product B run time is 3.5 hours, because product was manufactured 3.5 hrs Tue 11/12/18
For Product C run time is 1 hour, because product was manufactured 0.5 hours Tue 11/12/18 and 0.5 hours Wed 12/12/18
For Product D run time is 8 hours, because product was manufactured 7.5 hours Wed 12/12/18, 0 hours Thur 13/12/18 as it was day off (that's why shift start and end time is same in columns F:G), and 0.5 hour Friday 14/12/18
For Product E run time is 13.25 hours, because product was manufactured 7.5 hours Friday 14/12/18 and 5.75 hours Saturday 15/12/18

Any help would be greatly appreciated!
[TABLE="width: 923"]
<tbody>[TR]
[TD]Rows/Col[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]Product[/TD]
[TD="align: center"]Start Time[/TD]
[TD="align: center"]End Time[/TD]
[TD="align: center"]Run Time[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Shift Start[/TD]
[TD="align: center"]Shift End[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Product A[/TD]
[TD]Mon 10/12/2018 08:30[/TD]
[TD]Tue 11/12/2018 12:00[/TD]
[TD]13.5[/TD]
[TD][/TD]
[TD]Mon 10/12/2018 08:00[/TD]
[TD]Mon 10/12/2018 12:00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Product B[/TD]
[TD]Tue 11/12/2018 12:00[/TD]
[TD]Tue 11/12/2018 15:30[/TD]
[TD]3.5[/TD]
[TD][/TD]
[TD]Tue 11/12/2018 06:00[/TD]
[TD]Tue 11/12/2018 16:00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Product C[/TD]
[TD]Tue 11/12/2018 15:30[/TD]
[TD]Wed 12/12/2018 08:30[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Wed 12/12/2018 08:00[/TD]
[TD]Wed 12/12/2018 16:00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Product D[/TD]
[TD]Wed 12/12/2018 08:30[/TD]
[TD]Fri 14/12/2018 06:30[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]Thu 13/12/2018 06:00[/TD]
[TD]Thu 13/12/2018 06:00[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Product E[/TD]
[TD]Fri 14/12/2018 06:30[/TD]
[TD]Sat 15/12/2018 11:45[/TD]
[TD]13.25[/TD]
[TD][/TD]
[TD]Fri 14/12/2018 06:00[/TD]
[TD]Fri 14/12/2018 16:00[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sat 15/12/2018 06:00[/TD]
[TD]Sat 15/12/2018 12:00[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sun 16/12/2018 06:00[/TD]
[TD]Sun 16/12/2018 06:00[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mon 17/12/2018 06:00[/TD]
[TD]Mon 17/12/2018 16:00[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Tue 18/12/2018 06:00[/TD]
[TD]Tue 18/12/2018 06:00[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Wed 19/12/2018 07:00[/TD]
[TD]Wed 19/12/2018 11:00[/TD]
[/TR]
</tbody>[/TABLE]

 

[Koza84]

Bump

 

[Koza84]

Bump

 

[Koza84]

Still looking for solution. Anyone could help please?

 

[Koza84]

Bump. Could anyone help please?

 

[Koza84]

Bump bump. Is the result impossible to achieve?