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.

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​
1​	Day	Date	StartTime	EndTime	Hours		ShiftStart​	ShiftEnd​
2​	Monday	10-12-2018​	8:30​	16:00​	7,50​		6:00​	16:00​
3​	Tuesday	11-12-2018​	6:00​	12:00​	6,00​
4​	Tuesday	11-12-2018​	12:00​	15:30​	3,50​
5​	Tuesday	11-12-2018​	15:30​	16:00​	0,50​
6​	Wednesday	12-12-2018​	8:00​	8:30​	0,50​
7​	Wednesday	12-12-2018​	8:30​	16:00​	7,50​
8​	Friday	14-12-2018​	6:00​	6:30​	0,50​
9​	Friday	14-12-2018​	6:30​	16:00​	9,50​
10​	Saturday	15-12-2018​	6:00​	11:45​	5,75​

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!

Rows/Col	A	B	C	D	E	F	G
1	Product	Start Time	End Time	Run Time		Shift Start	Shift End
2	Product A	Mon 10/12/2018 08:30	Tue 11/12/2018 12:00	13.5		Mon 10/12/2018 08:00	Mon 10/12/2018 12:00
3	Product B	Tue 11/12/2018 12:00	Tue 11/12/2018 15:30	3.5		Tue 11/12/2018 06:00	Tue 11/12/2018 16:00
4	Product C	Tue 11/12/2018 15:30	Wed 12/12/2018 08:30	1		Wed 12/12/2018 08:00	Wed 12/12/2018 16:00
5	Product D	Wed 12/12/2018 08:30	Fri 14/12/2018 06:30	8		Thu 13/12/2018 06:00	Thu 13/12/2018 06:00
6	Product E	Fri 14/12/2018 06:30	Sat 15/12/2018 11:45	13.25		Fri 14/12/2018 06:00	Fri 14/12/2018 16:00
7						Sat 15/12/2018 06:00	Sat 15/12/2018 12:00
8						Sun 16/12/2018 06:00	Sun 16/12/2018 06:00
9						Mon 17/12/2018 06:00	Mon 17/12/2018 16:00
10						Tue 18/12/2018 06:00	Tue 18/12/2018 06:00
11						Wed 19/12/2018 07:00	Wed 19/12/2018 11:00

<tbody>
</tbody>

 

[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?