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.

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