OT Hours Calculation as per conditions.

[mmr1]

Hi,

A formula help is needed to return a result for columns E and F based on start and finish times specified in columns C and D.

In actual working days, the OT01 hours should be calculated below the finish time at 9 PM.

The OT02 is calculated after 9 PM and the total number of hours spent on Sundays and holidays falls under OT02.

Sample sheet with expected results are enclosed.

Book1
	A	B	C	D	E	F
3					Answer expected
4	Date	Day	Start Time	Finish Time	Normal Days Hours < = 9 PM	>9 PM& /Sunday/Holiday Hours
5	14-Feb-23	Tuesday	5:30 PM	12:30 PM	3.5	15.5
6	14-Feb-23	Tuesday	5:30 PM	10:00 PM	3.5	1
7	4-Mar-23	Saturday	11:00 PM	7:00 AM	0	8
8	10-Mar-23	Friday	10:00 PM	1:30 AM	0	3.5
9	5-Mar-23	Sunday	7:00 AM	5:00 PM	0	10
10	17-Apr-23	Monday	1:00 AM	4:00 AM	0	3
11
Sheet1

Cell Formulas
Range	Formula
B5:B10	B5	=TEXT(WEEKDAY(A5,1),"dddd")

Thanks,

 

[mmr1]

many thanks for your formula,

In almost all situations, the formula works well except for a few issues that are listed below in sample sheet.

OT HOURS.xlsx
	A	B	C	D	E	F	G	H
14							Answer expected	Answer expected
15		Holidays	14-02-2023		OT1	OT2	OT1	OT2
16	18-04-2023	Tuesday	11:00:00 PM	7:00:00 AM	-2	10	0	8
17	19-04-2023	Wednesday	10:00:00 PM	6:30:00 AM	-1	9.5	0	8.5
18	20-04-2023	Thursday	11:00:00 PM	5:30:00 AM	-2	8.5	0	6.5
Sheet1

Cell Formulas
Range	Formula
E16:E18	E16	=IF(AND(NOT(AND($C16>TIME(21,0,0),$D16<TIME(5,30,0))),WEEKDAY($A16,2)<>7,COUNTIF($C$15,$A16)=0,WORKDAY.INTL($A16,0,"1111110")=$A16,NOT(AND($C16<TIME(7,0,0),$D16<TIME(7,0,0)))),(MIN($D16+(D16<$C16),TIME(21,0,0))-MAX($C16,TIME(5,30,0)))*24,0)
F16:F18	F16	=ABS(D16+(D16<C16)-C16)*24-E16
B16:B18	B16	=TEXT(WEEKDAY(A16,1),"dddd")

 

[Biz]

Hope this solves your problems.

OT Hours Calculation as per conditions (Awesome).xlsx
	A	B	C	D	E	F	G	H
3		Holidays	14-Feb-23				Answer expected
4	Date	Day	Start Time	Finish Time	Normal Days Hours < = 9 PM	>9 PM& /Sunday/Holiday Hours	Normal Days Hours < = 9 PM	>9 PM& /Sunday/Holiday Hours
5					OT1	OT2	OT1	OT2
6	14-Feb-23	Tuesday	17:30:00	12:30:00	0	19		19
7	17-Feb-23	Friday	17:30:00	22:00:00	3.5	1	3.5	1
8	04-Mar-23	Saturday	17:30:00	7:00:00	3.5	10	3.5	10
9	10-Mar-23	Friday	22:00:00	1:30:00	0	3.5	0	3.5
10	05-Mar-23	Sunday	7:00:00	17:00:00	0	10	0	10
11	15-Apr-23	Saturday	5:00:00	6:30:00	0	1.5	0	1.5
12	18-Apr-23	Tuesday	23:00:00	7:00:00	0	8	0	8
13	19-Apr-23	Wednesday	22:00:00	6:30:00	0	8.5	0	8.5
14	20-Apr-23	Thursday	23:00:00	5:30:00	0	6.5	0	6.5
Sheet2

Cell Formulas
Range	Formula
E6:E14	E6	=MAX(IF(AND(NOT(AND($C6>TIME(21,0,0),$D6<TIME(5,30,0))),WEEKDAY($A6,2)<>7,COUNTIF($C$3,$A6)=0,WORKDAY.INTL($A6,0,"1111110")=$A6,NOT(AND($C6<TIME(7,0,0),$D6<TIME(7,0,0)))),(MIN($D6+(D6<$C6),TIME(21,0,0))-MAX($C6,TIME(5,30,0)))*24,0),0)
F6:F14	F6	=ABS(D6+(D6<C6)-C6)*24-E6
B6:B14	B6	=TEXT(WEEKDAY(A6,1),"dddd")

 

[mmr1]

Thank you so much for the formula solution,

It's working fine.

 

[Biz]

Thank you so much for the formula solution,

It's working fine.

Thank you for your feedback.