Sum time over night

[Simbor2]

Hi,
My shedule for my workers have worked so far but now there is a shift ending after midnight and I don't know how to fix it.
This is how it looks now. A formula to take a 30 min lunch away. The formula is in Swedish, but maybe still better to understand than a picture.

Timanställda schema 2023.xlsx
	B	C	D	E	F
52			0:00
53	13:00	22:00	8:30		Oskar
54	13:00	22:00	8:30		Mohammed
55	16:00	01:00	#########		Amged
56	16:00	01:00	#########		Jesca
57	16:00	01:00	#########		Ali A
58	16:00	01:00	#########		Maya
59			0:00
Vecka 23

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F47:F58	Cell	contains a blank value	text	NO
F59	Cell	contains a blank value	text	NO
D36:D45,D61:D70,D72:D81,D14:D23,D3:D12,D25:D34,D47:D59	Cell Value	=0	text	NO

 

[Simbor2]

Hello. Try with:

Libro1
	B	C	D	E	F
53	13:00	16:00	3:00		Oskar
Hoja1

Cell Formulas
Range	Formula
D53	D53	= (B53>C53)+C53-B53 - (6*((B53>C53)+C53-B53)>1)/48

Worked!! Thanks a lot!!

 

[Mario_R]

In this case, you should indicate which post resolved the issue and indicate that the issue is resolved.

 

[awoohaw]

@Simbor2
I've found that always keeping a date with the time makes for better date/time math.
Below is your chart with a date/time component for your subtraction. The top looks like your worksheet but it is really the bottom worksheet with the date and time combined:

mr excel questions 29.xlsm
	A	B	C	D	E	F	G
1	Start Time	End Time	0
2	13:00:00	22:00:00	09:00:00		Oskar
3	13:00:00	22:00:00	09:00:00		Mohammed
4	16:00:00	01:00:00	09:00:00		Amged
5	16:00:00	01:00:00	09:00:00		Jesca
6	16:00:00	01:00:00	09:00:00		Ali A
7	16:00:00	01:00:00	09:00:00		Maya
8
9
10
11	Start Day	Start Time	End Day	End Time	0
12	2023-04-20	13:00:00	2023-04-20	22:00:00	09:00:00		Oskar
13	2023-04-20	13:00:00	2023-04-20	22:00:00	09:00:00		Mohammed
14	2023-04-20	16:00:00	2023-04-21	01:00:00	09:00:00		Amged
15	2023-04-20	16:00:00	2023-04-21	01:00:00	09:00:00		Jesca
16	2023-04-20	16:00:00	2023-04-21	01:00:00	09:00:00		Ali A
17	2023-04-20	16:00:00	2023-04-21	01:00:00	09:00:00		Maya
18
Simbor2