Time Calc

[HappyChappy]

c3 and d3 are start and finish times
e3=MROUND(C3,(1/24/60)*15)
f3=MROUND(D3,(1/24/60)*15)
g3=MEDIAN("05:00","23:00",IF(F3<E3,1+F3,F3))-MEDIAN("05:00","23:00",E3)
g3 gives me correct time worked but i want to be able to deduct 30 mins from the total any help with this one please

 

[MARK858]

Try...

=MEDIAN("05:00","23:00",IF(F3<E3,1+F3,F3))-MEDIAN("05:00","23:00",E3)-TIMEVALUE("00:30:00")

Format the cell as a time format

 

[HappyChappy]

Try...

=MEDIAN("05:00","23:00",IF(F3<E3,1+F3,F3))-MEDIAN("05:00","23:00",E3)-TIMEVALUE("00:30:00")

Format the cell as a time format

it works but.... if the cells have no times entered eg 00:00 and 00:00 then it returns ############## can we get this removed

 

[MARK858]

=IF(OR(C3=0,D3=0),"",MEDIAN("05:00","23:00",IF(F3<E3,1+F3,F3))-MEDIAN("05:00","23:00",E3)-TIMEVALUE("00:30:00"))

 

[HappyChappy]

Essex23.24.xlsx
	A	B	C	D	E	F	G
2	June		Hours Worked				Total Hours
3	Monday	29/05/2023	00:00	00:00	0:00	00:00
4	Tuesday	30/05/2023	00:00	00:00	0:00	00:00
5	Wednesday	31/05/2023	06:00	16:15	6:00	16:15	#VALUE!
6	Thursday	01/06/2023	06:00	16:45	6:00	16:45	#VALUE!
7	Friday	02/06/2023	06:00	16:00	6:00	16:00	#VALUE!
8	Saturday	03/06/2023	00:00	00:00	0:00	00:00
9	Sunday	04/06/2023	00:00	00:00	0:00	00:00
10						#VALUE!	#VALUE!
June

Cell Formulas
Range	Formula
A3:A9	A3	=B3
E3:F9	F3	=MROUND(D3,(1/24/60)*15)
G3:G9	G3	=IF(OR(C3=0,D3=0),"",MEDIAN("05:00","23:00",IF(F3<E3,1+F3,F3))-MEDIAN("05:00","23:00",E3)-TIMEVALUE("00:30:00"""))
F10	F10	=SUM(G3:G9)
G10	G10	=CONCATENATE(24*DAY(F10)+HOUR(F10),":",MINUTE(F10))
B4:B9	B4	=B3+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:D9	Cell Value	>0	text	NO

 

[MARK858]

Not the result that I get with the formula that I posted

Book1.xlsb
	A	B	C	D	E	F	G	H
1
2	June		Hours Worked				Total Hours
3	45075	29/05/2023	0	0	0	0
4	45076	30/05/2023	0	0	0	0
5	45077	31/05/2023	0.25	0.677083	0.25	0.677083	09:45:00
6	45078	01/06/2023	0.25	0.697917	0.25	0.697917	10:15:00
7	45079	02/06/2023	0.25	0.666667	0.25	0.666667	09:30:00
8	45080	03/06/2023	0	0	0	0
9	45081	04/06/2023	0	0	0	0
10						1.229167	29:30
11
12
13
14
15
Sheet2

Cell Formulas
Range	Formula
A3:A9	A3	=B3
E3:F9	F3	=MROUND(D3,(1/24/60)*15)
G3:G9	G3	=IF(OR(C3=0,D3=0),"",MEDIAN("05:00","23:00",IF(F3<E3,1+F3,F3))-MEDIAN("05:00","23:00",E3)-TIMEVALUE("00:30:00"))
F10	F10	=SUM(G3:G9)
G10	G10	=CONCATENATE(24*DAY(F10)+HOUR(F10),":",MINUTE(F10))
B4:B9	B4	=B3+1

 

[HappyChappy]

noticed a typo in my copying of your formula to many speech marks

it works now

 

[MARK858]

Happy you got it working

 

[HappyChappy]

Essex23.24.xlsx
	A	B	C	D	E	F	G
1	Day	Date	Start/Finish Times		Rounded Time to nearest 15 Min	Rounded Time to nearest 15 Min	Hours Worked
2	June		Hours Worked				Total Hours
3	Monday	29/05/2023	00:00	00:00	0:00	00:00
4	Tuesday	30/05/2023	00:00	00:00	0:00	00:00
5	Wednesday	31/05/2023	06:00	16:15	6:00	16:15	9:45
6	Thursday	01/06/2023	06:00	16:45	6:00	16:45	10:15
7	Friday	02/06/2023	06:00	16:00	6:00	16:00	9:30
8	Saturday	03/06/2023	00:00	00:00	0:00	00:00
9	Sunday	04/06/2023	00:00	00:00	0:00	00:00
10						05:30	29:30
June

Cell Formulas
Range	Formula
A3:A9	A3	=B3
E3:F9	F3	=MROUND(D3,(1/24/60)*15)
G3:G9	G3	=IF(OR(C3=0,D3=0),"",MEDIAN("05:00","23:00",IF(F3<E3,1+F3,F3))-MEDIAN("05:00","23:00",E3)-TIMEVALUE("00:30:00"))
F10	F10	=SUM(G3:G9)
G10	G10	=CONCATENATE(24*DAY(F10)+HOUR(F10),":",MINUTE(F10))
B4:B9	B4	=B3+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:D9	Cell Value	>0	text	NO

 

[HappyChappy]

thankyou for your expertize