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]

Anyone would be able to help please ?

Best regards

 

[Koza84]

Aldo to mention that vba solution would work too if possible

 

[Koza84]

Apologies for bumbing it again but still looking for solution. Is the result not possible to achieve ?

 

[Koza84]

Bump

 

[Koza84]

Hi there.

Is it not possible to achieve results or is my approach not right?

Best regards

 

[Koza84]

Good Morning All

I wonder if I have explained results well?

Is the solution to complex to achieve?

Please any let me know maybe I should try different approach?

Best regards

 

[Koza84]

Hi.

Stil trying to fine solution.

Could anyone provide any feedback at all?

Best regards

 

[Koza84]

Bump

 

[strooman]

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.