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,

 

[Fluff]

What is the start time for OT01?
Also what are the holidays?

 

[mmr1]

What is the start time for OT01?
Also what are the holidays?

The OT01 hours are calculated after start time 5:30 PM and finish time at 9 PM on normal working days.

OT Hours Calculation as per conditions..xlsx
	A	B	C	D	E	F
3		Holidays	14-Feb-23		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")

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F
1
2
3		Holidays	44971		Answer expected
4	Date	Day	Start Time	Finish Time	Normal Days Hours < = 9 PM	>9 PM& /Sunday/Holiday Hours
5	44971	Tuesday	17:30:00	12:30:00	3.5	15.5
6	44971	Tuesday	17:30:00	22:00:00	3.5	1
7	44989	Saturday	23:00:00	07:00:00	0	8
8	44995	Friday	22:00:00	01:30:00	0	3.5
9	44990	Sunday	07:00:00	17:00:00	0	10
10	45033	Monday	01:00:00	04:00:00	0	3
Main

Cell Formulas
Range	Formula
E5:E9	E5	=IF(OR(C5>TIME(21,0,0),WEEKDAY(A5,2)>5),0,(MIN(D5+(D5<C5),TIME(21,0,0))-MAX(C5,TIME(5,30,0)))*24)
F5:F10	F5	=ABS(D5+(D5<C5)-C5)*24-E5
E10	E10	=IF(OR(C10>TIME(21,0,0),D10<TIME(5,30,0),WEEKDAY(A10,2)>5),0,(MIN(D10+(D10<C10),TIME(21,0,0))-MAX(C10,TIME(5,30,0)))*24)
B5:B10	B5	=TEXT(WEEKDAY(A5,1),"dddd")

 

[mmr1]

Many thanks for your formula solution.

Could you please include E10 also in single formula instead of separate formula.

 

[Biz]

Try formula cell E5 and copy down E10
=IF(OR(C5>TIME(21,0,0), D5<TIME(5,30,0), WEEKDAY(A5,2)>5), 0, (MIN(D5+(D5<C5), TIME(21,0,0)) - MAX(C5, TIME(5,30,0))) * 24)

Biz

 

[mmr1]

Thanks for the help,

Could you please look into row number 7 results, which are actually returning as shown in the expected result in G:7 , and Saturday is considered a working day,

The 14th of February 2023 is a holiday and all working hours will be considered as OT2 hours.

Book1
	A	B	C	D	E	F	G	H
3		Holidays	14-02-2023				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	14-02-2023	Tuesday	5:30:00 PM	12:30:00 PM	3.5	15.5		19
6	17-02-2023	Friday	5:30:00 PM	10:00:00 PM	3.5	1	3.5	1
7	04-03-2023	Saturday	5:30:00 PM	7:00:00 AM	0	13.5	3.5	10
8	10-03-2023	Friday	10:00:00 PM	1:30:00 AM	0	3.5	0	3.5
9	05-03-2023	Sunday	7:00:00 AM	5:00:00 PM	0	10	0	10
10	15-04-2023	Saturday	5:00:00 AM	6:30:00 AM	0	1.5	0	1.5
11
Sheet1

Cell Formulas
Range	Formula
E5:E10	E5	=IF(OR(C5>TIME(21,0,0), D5<TIME(5,30,0), WEEKDAY(A5,2)>5), 0, (MIN(D5+(D5<C5), TIME(21,0,0)) - MAX(C5, TIME(5,30,0))) * 24)
F5:F10	F5	=ABS(D5+(D5<C5)-C5)*24-E5
B5:B10	B5	=TEXT(WEEKDAY(A5,1),"dddd")

 

[Biz]

Hi,
If I understand correctly, Monday through Saturday are considered working days. In that case, I believe E10 should have 1.5 hours of normal work hours without any overtime.
Try formula below in Cell E5 and copy down E10
=IF(AND(NOT(OR($C5>TIME(21,0,0),$D5<TIME(5,30,0))),WEEKDAY($A5,2)<>7, COUNTIF($C$3, $A5)=0,WORKDAY.INTL($A5,0,"1111110")=$A5),(MIN(D5+(D5<C5),TIME(21,0,0))-MAX(C5,TIME(5,30,0)))*24, 0)

Does it help?

Biz

 

[mmr1]

I appreciate your formula solution, thank you,

The normal working hours are 7 a.m. to 5:30 p.m.

Time for OT 1 Hours is 5:30 p.m. to 9:00 p.m. on working day

The OT2 hours are between 9:00 PM and 7:AM. on working day

The full working hours of Sundays and holidays will be considered as OT2 hours

Therefore, E:10 Hours are part of OT2 Hours because they fall between 9:PM and 7:AM

Thanks,

 

[Biz]

Try formula below in Cell B5 and copy it down E10.

=IF(AND(NOT(AND($C5>TIME(21,0,0),$D5<TIME(5,30,0))),WEEKDAY($A5,2)<>7,COUNTIF($C$3,$A5)=0,WORKDAY.INTL($A5,0,"1111110")=$A5,NOT(AND($C5<TIME(7,0,0),$D5<TIME(7,0,0)))),(MIN($D5+(D5<$C5),TIME(21,0,0))-MAX($C5,TIME(5,30,0)))*24,0)

Hope it helps.

Biz