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]

You're welcome

 

[HappyChappy]

Essex23.24.xlsx
	A	B	C	D	E	F	G	H
1	Day	Date	Start/Finish Times		Rounded Time to nearest 15 Min	Rounded Time to nearest 15 Min	Hours Worked	Paid
2	June		Hours Worked				Total Hours	Paid
3	Monday	29/05/2023	00:00	00:00	0:00	00:00		£150.00
4	Tuesday	30/05/2023	00:00	00:00	0:00	00:00		£150.00
5	Wednesday	31/05/2023	06:00	16:15	6:00	16:15	9:45	£150.00
6	Thursday	01/06/2023	06:00	16:45	6:00	16:45	10:15	£150.00
7	Friday	02/06/2023	06:00	16:00	6:00	16:00	9:30	£150.00
8	Saturday	03/06/2023	00:00	00:00	0:00	00:00		£150.00
9	Sunday	04/06/2023	00:00	00:00	0:00	00:00		£150.00
10						05:30	29:30	£1,050.00
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"))
H3:H9	H3	=IF($G3>0,150,"")
F10	F10	=SUM(G3:G9)
G10	G10	=CONCATENATE(24*DAY(F10)+HOUR(F10),":",MINUTE(F10))
H10	H10	=SUM(H3:H9)
B4:B9	B4	=B3+1

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

your formula works great but it has thrown up an error in col H as you can see its giving me £150 for zero hours worked

 

[HappyChappy]

Essex23.24.xlsx
	A	B	C	D	E	F	G	H
1	Day	Date	Start/Finish Times		Rounded Time to nearest 15 Min	Rounded Time to nearest 15 Min	Hours Worked	Paid
2	June		Hours Worked				Total Hours	Paid
3	Monday	29/05/2023	00:00	00:00	0:00	00:00		£150.00
4	Tuesday	30/05/2023	00:00	00:00	0:00	00:00		£150.00
5	Wednesday	31/05/2023	06:00	16:15	6:00	16:15	9:45	£150.00
6	Thursday	01/06/2023	06:00	16:45	6:00	16:45	10:15	£150.00
7	Friday	02/06/2023	06:00	16:00	6:00	16:00	9:30	£150.00
8	Saturday	03/06/2023	00:00	00:00	0:00	00:00		£150.00
9	Sunday	04/06/2023	00:00	00:00	0:00	00:00		£150.00
10						05:30	29:30	£1,050.00
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"))
H3:H9	H3	=IF($G3>0,150,"")
F10	F10	=SUM(G3:G9)
G10	G10	=CONCATENATE(24*DAY(F10)+HOUR(F10),":",MINUTE(F10))
H10	H10	=SUM(H3:H9)
B4:B9	B4	=B3+1

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

Works great but it has thown up an error in Col H it now pays me on days not worked would be nice if this was the case..

 

[MARK858]

=IF($G3<>"",150,"")

 

[HappyChappy]

you Sir are marvelous

 

[MARK858]

You're welcome

 

[smozgur]

you Sir are marvelous

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.