Criteria for NS and DS time

[nazarene]

Dear sir,
I have this problem for creating formula,
Day Shift= 7:00to 17:00
In Day Shift all time before 7:00 will consider & 7:00am, while after 7:00 will take the actual, example the employee comes at 6:45 it will consider as 7:00, but if the employees comes 7:25 it will take 7:25

Night shift
Night shift will start at 21:00 up to 6:00 in the morning, anytime out of that will be OT
the below is my table

 

[JamesCanale]

MrExcelPlayground.xlsm
	J	K	L	M	N	O	P	Q
3	Name	Date in	Time in	Effective Time in	Date out	Time out	Total	OT hours
4	Harry	12/20/2020	7:36	7:36	12/20/2020	18:04	10:28	0:00
5	Fred	12/20/2020	6:45	7:00	12/20/2020	21:30	14:30	0:30
6	Mary	12/20/2020	6:55	7:00	12/21/2020	1:00	18:00	4:00
Sheet29

Cell Formulas
Range	Formula
P4:P6	P4	=(N4+O4)-(K4+M4)
Q4:Q6	Q4	=MAX((N4+O4)-(K4+21/24),0)
M4:M6	M4	=MAX(L4,7/24)

It won't work right for shifts longer than 24 hours.

 

[nazarene]

Sir how about if we started at
21:00 to 6:00 (this is for Night shift) how to compute in reverse

 

[nazarene]

I mean time in is started at 21:00 up to 6:00 for night shift and the excess will be OT

 

[JamesCanale]

MrExcelPlayground.xlsm
	J	K	L	M	N	O	P	Q
12	Name	Date in	Time in	Effective Time in	Date out	Time out	Total	OT hours
13	Harry	12/20/2020	21:36	21:36	12/21/2020	6:00	8:24	0:00
14	Fred	12/20/2020	20:45	21:00	12/21/2020	7:00	10:00	1:00
15	Mary	12/20/2020	20:56	21:00	12/21/2020	9:00	12:00	3:00
Sheet29

Cell Formulas
Range	Formula
P13:P15	P13	=(N13+O13)-(K13+M13)
Q13:Q15	Q13	=MAX((N13+O13)-(N13+6/24),0)
M13:M15	M13	=MAX(L13,21/24)

 

[nazarene]

Dear Sir james,

Thank you so much , you are really excel black belter



Regards,
Nazarene

 

[nazarene]

Dear Sir James,
One more Criteria , I want to know, because in your figure below
there should be no OT, because the starts of work is 7 am until 5 pm, it means 10 hours of work as regular, he works at 9:45am until 5:09pm, so he did not achieve the 10 hours normal, 7hr. and 24 minutes , so he has 0 OT. Can you pelase help me again to include that formula

Effective Date

Time Out

Total Hours

OT Hours

9:45:00 AM

5:09:06 PM

7:24

0:09

 

[JamesCanale]

I don't understand the OT rules. I currently have any hours after 21:00 (9pm). Is it any hours past 10 working hours? That would be easier.

 

[nazarene]

Good day to you again Mr. James.
Can you please help me to make a formula related as per below table, now I am mannualy doing the process,
the logic is like this, I would like to get only the hours spent from the range of 9:00pm to 6:00am.

For example:
In item # 1- The workers starts to work from 6:27am until 10:09pm so the 9pm to 10:09pm equal hours spent will be 1 hours and 9 minutes
In item # 2 - The Workers starts to work from 4:15am until 12:17pm so the 4:15am to 6:00am equal hours spent will be 1 hours and 45 minutes

Thank you and best regards

 

[JamesCanale]

I'm not sure what version of excel you have - if it's 365, this will work:

Mr Excel Playground 3.xlsm
	A	B	C	D	E	F
1						Between 9pm and 6am
2	1	6/26/2021	6:27:46	6/26/2021	23:09:59	2:09
3	2	7/27/2021	4:15:47	7/27/2021	12:17:25	1:45
4	3	7/27/2021	3:45:20	7/27/2021	13:28:15	2:15
5	4	6/26/2021	6:27:39	6/26/2021	22:09:53	1:09
6	5	7/27/2021	3:54:20	7/27/2021	12:05:42	2:06
7	6	7/27/2021	20:00:00	7/28/2021	8:00:00	9:00
Sheet20

Cell Formulas
Range	Formula
F2:F7	F2	=(COUNT(MATCH(MOD(SEQUENCE(((D2+E2)-(B2+C2))*60*24,1,INT(C2*60*24),1),1440),SEQUENCE(360,1,0,1),0))+COUNT(MATCH(MOD(SEQUENCE(((D2+E2)-(B2+C2))*60*24,1,INT(C2*60*24),1),1440),SEQUENCE(180,1,1260,1),0)))/24/60