Overtime 7pm to 7am

[nickspray]

Hi,

I'm trying to find a formula that pulls out hours from Start and Ends times of a shift, that fall between 19:00hrs and 07:00hrs (this is our overtime range). The format from our system has both date and hours (in 24hr), which adds to the pain...lol

The example in yellow would return 8.5 hours overtime. The difference would be 2 hours Ordinary time (which I can deal with). We also have overtime all weekends, so recognizing the date to return all hours as OT for this would also be great...

Appreciate anyone providing any assistance at all.

Cheers

 

[Dave Patton]

There are probably many examples of overtime formulas in the forum; try searching for ideas.
It is very helpful for people who try to help if you post a concise example with the forum's tool named XL2BB.

The following may help.

Time 2023.xlsm
	A	B	C	D	E	F
1		Overtime	19:00	07:00
2
3
4				Total	Overtime	Regular
5		Wed 01-Mar-23 18:30	02-Mar-23 02:30	8.00	7.50	0.50
6		Wed 01-Mar-23 17:00	02-Mar-23 03:30	10.50	8.50	2.00
7		Sat 04-Mar-23 15:00	05-Mar-23 03:30	12.50	12.50	0.00
8
1c

Cell Formulas
Range	Formula
D5:D7	D5	=(C5-B5)*24
E5:E6	E5	=(MIN(INT(C5)+$D$1,C5)-MAX(INT(B5)+$C$1,B5))*24
F5:F7	F5	=D5-E5
E7	E7	=IF(WEEKDAY(B7,2)>5,(C7-B7)*24,(MIN(INT(C7)+$D$1,C7)-MAX(INT(B7)+$C$1,B7))*24)

You can paste the example to a clean sheet.

 

[rlv01]

Maybe something like this.

TimeTest.xlsm
	A	B	C	D	E	F	G	H	I
1	Settings
2	Night Shift Start	19:00
3	Night Shift Stop	7:00
4
5							Hidden Column	Hidden Column	Hidden Column
6	Start Time	End Time	Next Day	Total Work	Day Rate Hours	Night Rate Hours	Stop (Adjusted)	Night Shift Start	Night Shift Stop
7	6:00	19:30	0	13.5	12.50	1	19:30	6:00	7:00
8	6:30	19:00	0	12.5	12.00	0.5	19:00	6:30	7:00
9	18:00	2:30	1	8.5	1.00	7.5	2:30	19:00	2:30
10	1:30	4:30	0	3	3.00	0	4:30	1:30	7:00
11	17:00	3:30	1	10.5	2.00	8.5	3:30	19:00	3:30
Sheet4 (2)

Cell Formulas
Range	Formula
C7:C11	C7	=IF(B7<$A7,1,0)
D7:D11	D7	=(G7-A7)*24
E7:E11	E7	=D7-F7
F7:F11	F7	=IF(ROUND((I7-H7)*24,2)>D7,0,IF(TRIM(A7)&TRIM(B7)="",0,ROUND(IF((I7-H7)*24<0,0,(I7-H7)*24),2)))
G7:G11	G7	=IF(C7=1,B7+1,B7)
H7:H11	H7	=IF(C7=1,IF(A7>$B$2,A7,$B$2),IF(A7>=$B$3,$B$2,A7))
I7:I11	I7	=IF(C7=1,IF(G7<$B$3+1,G7,$B$3+1),IF(AND(A7>=$B$3,G7 > $B$3),G7,$B$3))

 

[nickspray]

There are probably many examples of overtime formulas in the forum; try searching for ideas.
It is very helpful for people who try to help if you post a concise example with the forum's tool named XL2BB.

The following may help.

Time 2023.xlsm
	A	B	C	D	E	F
1		Overtime	19:00	07:00
2
3
4				Total	Overtime	Regular
5		Wed 01-Mar-23 18:30	02-Mar-23 02:30	8.00	7.50	0.50
6		Wed 01-Mar-23 17:00	02-Mar-23 03:30	10.50	8.50	2.00
7		Sat 04-Mar-23 15:00	05-Mar-23 03:30	12.50	12.50	0.00
8
1c

Cell Formulas
Range	Formula
D5:D7	D5	=(C5-B5)*24
E5:E6	E5	=(MIN(INT(C5)+$D$1,C5)-MAX(INT(B5)+$C$1,B5))*24
F5:F7	F5	=D5-E5
E7	E7	=IF(WEEKDAY(B7,2)>5,(C7-B7)*24,(MIN(INT(C7)+$D$1,C7)-MAX(INT(B7)+$C$1,B7))*24)

You can paste the example to a clean sheet.

That's great, thank you very much. Only issue is that it doesn't seem to work when the start and end dates are the same. When the dates differ, if fires our negatives - see yellow below. Green is perfect

Cheers

 

[nickspray]

That's great, thank you very much. Only issue is that it doesn't seem to work when the start and end dates are the same. When the dates differ, if fires our negatives - see yellow below. Green is perfect

Cheers

View attachment 86910

*Sorry, when the dates are the SAME...

 

[nickspray]

Maybe something like this.

TimeTest.xlsm
	A	B	C	D	E	F	G	H	I
1	Settings
2	Night Shift Start	19:00
3	Night Shift Stop	7:00
4
5							Hidden Column	Hidden Column	Hidden Column
6	Start Time	End Time	Next Day	Total Work	Day Rate Hours	Night Rate Hours	Stop (Adjusted)	Night Shift Start	Night Shift Stop
7	6:00	19:30	0	13.5	12.50	1	19:30	6:00	7:00
8	6:30	19:00	0	12.5	12.00	0.5	19:00	6:30	7:00
9	18:00	2:30	1	8.5	1.00	7.5	2:30	19:00	2:30
10	1:30	4:30	0	3	3.00	0	4:30	1:30	7:00
11	17:00	3:30	1	10.5	2.00	8.5	3:30	19:00	3:30
Sheet4 (2)

Cell Formulas
Range	Formula
C7:C11	C7	=IF(B7<$A7,1,0)
D7:D11	D7	=(G7-A7)*24
E7:E11	E7	=D7-F7
F7:F11	F7	=IF(ROUND((I7-H7)*24,2)>D7,0,IF(TRIM(A7)&TRIM(B7)="",0,ROUND(IF((I7-H7)*24<0,0,(I7-H7)*24),2)))
G7:G11	G7	=IF(C7=1,B7+1,B7)
H7:H11	H7	=IF(C7=1,IF(A7>$B$2,A7,$B$2),IF(A7>=$B$3,$B$2,A7))
I7:I11	I7	=IF(C7=1,IF(G7<$B$3+1,G7,$B$3+1),IF(AND(A7>=$B$3,G7 > $B$3),G7,$B$3))

I couldn't quite get it to work...but thank you for your reply

 

[rlv01]

I couldn't quite get it to work...but thank you for your reply

Interesting. I've tested it and it appears to work fine for me. Keep in mind that my example only requires that you enter the start / end times (ex:16:30), not the full date+times (ex: 1/3/2023 16:30). I imagine that might throw things off.

BTW, by posting images of your data you are making less likely that you will get help. Many people otherwise inclined to help see that your data is not easily accessible and move on to someone else's problem because it is not worth the effort to to manually type in a lot of data in order to experiment with your problem. Consider using the free XL2BB tool (link below) to post sample data in a way that makes it accessible to others to copy and paste.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com