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

 

[etaf]

how about
=(MOD(B2-A2,1)*24)-0.5

Book7
	A	B	C	D	E
1			0
2	13:00:00	22:00:00	0	8.5	Oskar
3	13:00:00	22:00:00	0	8.5	Mohammed
4	16:00:00	01:00:00	0	8.5	Amged
5	16:00:00	01:00:00	0	8.5	Jesca
6	16:00:00	01:00:00	0	8.5	Ali A
7	16:00:00	01:00:00	0	8.5	Maya
8			0
Sheet1

Cell Formulas
Range	Formula
D2:D7	D2	=(MOD(B2-A2,1)*24)-0.5
C1:C8	C1	=IF(C52-B52>4/24,C52-B52-0.5/24,C52-B52)

 

[Simbor2]

how about
=(MOD(B2-A2,1)*24)-0.5

Book7
	A	B	C	D	E
1			0
2	13:00:00	22:00:00	0	8.5	Oskar
3	13:00:00	22:00:00	0	8.5	Mohammed
4	16:00:00	01:00:00	0	8.5	Amged
5	16:00:00	01:00:00	0	8.5	Jesca
6	16:00:00	01:00:00	0	8.5	Ali A
7	16:00:00	01:00:00	0	8.5	Maya
8			0
Sheet1

Cell Formulas
Range	Formula
D2:D7	D2	=(MOD(B2-A2,1)*24)-0.5
C1:C8	C1	=IF(C52-B52>4/24,C52-B52-0.5/24,C52-B52)

I'll try it out! Thanks!

 

[Simbor2]

how about

(REST = MOD in swedish)
This is the result. Don't understand it.

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

Cell Formulas
Range	Formula
D53:D54,D56:D58	D53	=IF(C53-B53>4/24,C53-B53-0.5/24,C53-B53)
D55	D55	=(MOD(C55-B55,1)*24)-0.5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F47:F58	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

Cells with Data Validation
Cell	Allow	Criteria
B53:C58	Time	between 00:00:00 and 23:59:00
F53:F58	List	=$K$85:$K$105

 

[etaf]

the cell is formatted as TIME
change the format to general for all cells in column D

Notice I get a decimal point
you get a colon

the result is just a number , not a time

 

[Simbor2]

the cell is formatted as TIME
change the format to general for all cells in column D

Notice I get a decimal point
you get a colon

the result is just a number , not a time

Is it possible to keep it as time?
Add a calculation to the whole formula?

 

[etaf]

=((MOD(B2-A2,1)*24)-0.5)/24

Book10
	A	B	C	D	E
1			0
2	0.541666667	0.916666667	0	08:30:00	Oskar
3	0.541666667	0.916666667	0	08:30:00	Mohammed
4	0.666666667	0.041666667	0	08:30:00	Amged
5	0.666666667	0.041666667	0	08:30:00	Jesca
6	0.666666667	0.041666667	0	08:30:00	Ali A
7	0.666666667	0.041666667	0	08:30:00	Maya
8			0
Sheet1

Cell Formulas
Range	Formula
D2:D7	D2	=((MOD(B2-A2,1)*24)-0.5)/24
C1:C8	C1	=IF(C52-B52>4/24,C52-B52-0.5/24,C52-B52)

 

[Simbor2]

=((MOD(B2-A2,1)*24)-0.5)/24

Book10
	A	B	C	D	E
1			0
2	0.541666667	0.916666667	0	08:30:00	Oskar
3	0.541666667	0.916666667	0	08:30:00	Mohammed
4	0.666666667	0.041666667	0	08:30:00	Amged
5	0.666666667	0.041666667	0	08:30:00	Jesca
6	0.666666667	0.041666667	0	08:30:00	Ali A
7	0.666666667	0.041666667	0	08:30:00	Maya
8			0
Sheet1

Cell Formulas
Range	Formula
D2:D7	D2	=((MOD(B2-A2,1)*24)-0.5)/24
C1:C8	C1	=IF(C52-B52>4/24,C52-B52-0.5/24,C52-B52)

Timanställda schema 2023.xlsx
	B	C	D	E	F
55	16:00	01:00	14:30		Amged
Vecka 23

Cell Formulas
Range	Formula
D55	D55	=((MOD(B55-C55,1)*24)-0.5)/24

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F47:F58	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

Cells with Data Validation
Cell	Allow	Criteria
B55:C55	Time	between 00:00:00 and 23:59:00

Should be 8:30

 

[etaf]

yep, mine is

change to general and see the decimal number

Book1
	A	B	C	D	E
1			0
2	0.541666667	0.916666667	0	0.354166667	Oskar
3	0.541666667	0.916666667	0	0.354166667	Mohammed
4	0.666666667	0.041666667	0	0.354166667	Amged
5	0.666666667	0.041666667	0	0.354166667	Jesca
6	0.666666667	0.041666667	0	0.354166667	Ali A
7	0.666666667	0.041666667	0	0.354166667	Maya
Sheet1

Cell Formulas
Range	Formula
C1:C7	C1	=IF(C52-B52>4/24,C52-B52-0.5/24,C52-B52)
D2:D7	D2	=((MOD(B2-A2,1)*24)-0.5)/24

perhaps its a change in countries - not sure why

perhaps put the sample spreadsheet onto a share..

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[Mario_R]

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