time equation

[HappyChappy]

i have 50:00 in cell c1 3:35 in c2 when i put c1-c2 i get 04:48 i should be getting 46:25 cant seem to figure it out sure there is a simple reason its not returning the correct figure

 

[awoohaw]

How is each cell formatted?
Can you convert to General and see if the arithmetic looks correct.
How did you enter the time values (manually or by TIME() formula)?

Posting a mini sheet of your data could be helpful.

This is what I have:

Book1
	A	B	C	D	E	F
1			50:00:00	00:50:00	00:50:00	50:00:00
2			03:35:00	00:03:35	03:35:00	00:03:35
3			46:25:00	0:46:25	###############	49:56:25
Sheet2

Cell Formulas
Range	Formula
C3:F3	C3	=C1-C2

 

[HappyChappy]

Payslip.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Day	Date	Start Time	Finish Time	Rounded Time to nearest 15 Min	Rounded Time to nearest 15 Min	Hours worked	Total_45 Break	Basic Hours	Basic + Servicing	Basic Hours + Breaks	Overtime	Paid at Std Rate		Servicing	break	Hours @ Overtime		Bonus	over time	Fuel Mileage
2			Days Hours
3	Monday	05/02/2024	07:00	17:00	07:00	17:00	10:00	9:15	8:00	8:30	8:45	0:57	131.75		0:30	0:45	0:45	1:15	£12.25	22.09	20.00
4	Tuesday	06/02/2024	07:00	17:00	07:00	17:00	10:00	9:15	8:00	8:30	8:45	0:57	131.75		0:30	0:45	0:45	1:15	£12.25	22.09	20.00
5	Wednesday	07/02/2024	07:00	17:00	07:00	17:00	10:00	9:15	8:00	8:30	8:45	0:57	131.75		0:30	0:45	0:45	1:15	£12.25	22.09	20.00
6	Thursday	08/02/2024	07:00	17:00	07:00	17:00	10:00	9:15	8:00	8:30	8:45	0:57	131.75		0:30	0:45	0:45	1:15	£12.25	22.09	20.00
7	Friday	09/02/2024	07:00	17:00	07:00	17:00	10:00	9:15	7:00	7:30	8:45	0:57	116.25	0.04	0:30	0:45	0:45	2:15	£12.25	22.09	20.00
8	Saturday	10/02/2024			00:00	00:00	0:00		0:00	0:00	0:00	0:00	0.00			0:00	0:00	0:00	£0.00	0.00
9	Sunday	11/02/2024			00:00	00:00	0:00		0:00	0:00	0:00	0:00	0.00			0:00	0:00	0:00	£0.00	0.00
10							2:00		15:00	17:30	19:45	4:45			1.25	0.16	3:45
11							50.0		39.0	41.30	43.45	4.45	643.25			3.45	3.45		61.25	110.44
12	Break	0:45:00									13:12
13	£15.50	STANDARD
14	£23.25	O/Time
15	£1.40	Bonus
16	£0.17	Fuel
17
050224 (2)

Cell Formulas
Range	Formula
E3:F9	E3	=MROUND(C3,(1/24/60)*15)
I3:I6,I8:I9	I3	=G3-O3-P3-Q3
J3:J9	J3	=I3+O3
I7	I7	=G7-O7-P7-Q7-N7
G10,P10:Q10,I10:L10	I10	=SUM(I3:I9)
I11:L11,G11,P11:Q11	I11	=CONCATENATE(24*DAY(I10)+HOUR(I10),".",MINUTE(I10))
P3:P9	P3	=G3-H3
Q3:Q9	Q3	=MOD(F3-E3,1)-H3
R3:R9	R3	=H3-I3
S3:S9	S3	=(K3*24)*A$15
T3:T9	T3	=(L3*24)*A$14
H3:H7	H3	=G3-B$12
O3:O7	O3	=O$10/60
B4:B9	B4	=B3+1
M3:M9	M3	=(J3*24)*A$13
L3:L9	L3	=MEDIAN("16:03","17:00",IF(F3<E3,1+F3,F3))-MEDIAN("16:03","17:00",E3)
G3:G9	G3	=MEDIAN("07:00","17:00",IF(F3<E3,1+F3,F3))-MEDIAN("07:00","17:00",E3)
M11,S11:T11	M11	=SUM(M3:M9)
K3:K6,K8:K9	K3	=I3+P3
K7	K7	=I7+P7+O7+O7
K12	K12	=G11-P11

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

G11 -p11= k12 but k12 should be 46.25.

 

[awoohaw]

Well, you are not subtracting TIME from TIME at this point.
Excel doesn't think the last part of the decimal can't exceed 59 hundredths.
it is subtracting .45 from 1.00 ( or .99 + .01).

 

[awoohaw]

This is what I come up with:

In cell K14:

=DAY(G13-P13)*24+HOUR(G13-P13)&TEXT(MINUTE(G13-P13)/60,".00")

NOTE: this is now text, if you want to use it as a value, you'll need to multiply by 1 or add 0.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Day	Date	Start Time	Finish Time	Rounded Time to nearest 15 Min	Rounded Time to nearest 15 Min	Hours worked	Total_45 Break	Basic Hours	Basic + Servicing	Basic Hours + Breaks	Overtime	Paid at Std Rate		Servicing	break	Hours @ Overtime		Bonus	over time	Fuel Mileage
2			Days Hours
3	Monday	45327	07:00:00	17:00:00	07:00:00	17:00:00	10:00:00	09:15:00	08:00:00	08:30:00	08:45:00	00:57:00	131.75		00:30:00	00:45:00	00:45:00	01:15:00	12.25	22.0875	20
4	Tuesday	45328	07:00:00	17:00:00	07:00:00	17:00:00	10:00:00	09:15:00	08:00:00	08:30:00	08:45:00	00:57:00	131.75		00:30:00	00:45:00	00:45:00	01:15:00	12.25	22.0875	20
5	Wednesday	45329	07:00:00	17:00:00	07:00:00	17:00:00	10:00:00	09:15:00	08:00:00	08:30:00	08:45:00	00:57:00	131.75		00:30:00	00:45:00	00:45:00	01:15:00	12.25	22.0875	20
6	Thursday	45330	07:00:00	17:00:00	07:00:00	17:00:00	10:00:00	09:15:00	08:00:00	08:30:00	08:45:00	00:57:00	131.75		00:30:00	00:45:00	00:45:00	01:15:00	12.25	22.0875	20
7	Friday	45331	07:00:00	17:00:00	07:00:00	17:00:00	10:00:00	09:15:00	07:00:00	07:30:00	08:45:00	00:57:00	116.25	01:00:00	00:30:00	00:45:00	00:45:00	02:15:00	12.25	22.0875	20
8	Saturday	45332			0	0	0		0	0	0	0	0			0	0	0	0	0
9	Sunday	45333			0	0	0		0	0	0	0	0			0	0	0	0	0
10							2.0833333333333300		1.625	1.72916667	1.822916667	0.197916667			1.25	0.15625000	0.15625
11		G11 -p11= k12 but k12 should be 46.25.					50.0		39.0	41.30	43.45	4.45	643.25			3.45	3.45		61.25	110.44
12	Break	0.03125									46.55
13	15.5	STANDARD			Format G10 as [HH:MM]		50:00									03:45
14	23.25	O/Time									46.25
15	1.4	Bonus
Sheet2

Cell Formulas
Range	Formula
E3:F9	E3	=MROUND(C3,(1/24/60)*15)
I3:I6,I8:I9	I3	=G3-O3-P3-Q3
J3:J9	J3	=I3+O3
I7	I7	=G7-O7-P7-Q7-N7
G10,P10:Q10,I10:L10	I10	=SUM(I3:I9)
I11:L11,G11,P11:Q11	I11	=CONCATENATE(24*DAY(I10)+HOUR(I10),".",MINUTE(I10))
P3:P9	P3	=G3-H3
Q3:Q9	Q3	=MOD(F3-E3,1)-H3
R3:R9	R3	=H3-I3
S3:S9	S3	=(K3*24)*A$15
T3:T9	T3	=(L3*24)*A$14
H3:H7	H3	=G3-B$12
O3:O7	O3	=O$10/60
B4:B9	B4	=B3+1
M3:M9	M3	=(J3*24)*A$13
L3:L9	L3	=MEDIAN("16:03","17:00",IF(F3<E3,1+F3,F3))-MEDIAN("16:03","17:00",E3)
G3:G9	G3	=MEDIAN("07:00","17:00",IF(F3<E3,1+F3,F3))-MEDIAN("07:00","17:00",E3)
M11,S11:T11	M11	=SUM(M3:M9)
K3:K6,K8:K9	K3	=I3+P3
K7	K7	=I7+P7+O7+O7
K12	K12	=G11-P11
G13	G13	=SUM(G3:G9)
P13	P13	=MROUND(SUM(P3:P9),15/(24*60))
K14	K14	=DAY(G13-P13)*24+HOUR(G13-P13)&TEXT(MINUTE(G13-P13)/60,".00")

 

[HappyChappy]

Payslip.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Day	Date	Start Time	Finish Time	Hours Worked	Break	Hours Minus Break	Servicing	Basic Hours	Basic Plus Servicing	Bonus Hours	Overtime		Adjust for 39 hours spread	Basic Pay	OverTime Pay	Bonus Pay	Breakdowns	Bonus Pay lost	Total Bonus Pay	Miles Travelled		Daily Total
2			Days Hours
3	Monday	05/02/2024	07:00	17:00	10:00:00	00:45:00	09:15:00	00:30:00	07:48:00	08:18:00	08:45:00	00:57:00	00:45:00	00:12:00	128.65	22.09	12.25		0.00	12.25	20.00	3.47	166.46
4	Tuesday	06/02/2024	07:00	17:00	10:00:00	00:45:00	09:15:00	00:30:00	07:48:00	08:18:00	08:45:00	00:57:00	00:45:00	00:12:00	128.65	22.09	12.25		0.00	12.25	20.00	3.47	166.46
5	Wednesday	07/02/2024	07:00	17:00	10:00:00	00:45:00	09:15:00	00:30:00	07:48:00	08:18:00	08:45:00	00:57:00	00:45:00	00:12:00	128.65	22.09	12.25		0.00	12.25	20.00	3.47	166.46
6	Thursday	08/02/2024	07:00	17:00	10:00:00	00:45:00	09:15:00	00:30:00	07:48:00	08:18:00	08:45:00	00:57:00	00:45:00	00:12:00	128.65	22.09	12.25		0.00	12.25	20.00	3.47	166.46
7	Friday	09/02/2024	07:00	17:00	10:00:00	00:45:00	09:15:00	00:30:00	07:48:00	08:18:00	08:45:00	00:57:00	00:45:00	00:12:00	128.65	22.09	12.25		0.00	12.25	20.00	3.47	166.46
8	Saturday	10/02/2024
9	Sunday	11/02/2024
10
11					02:00	03:45	22:15	02:30	15:00	17:30	19:45	04:45	03:45	01:00
12					50.0	3.45	46.15	2.30	39.0	41.30	43.45	4.45	3.45	1.0	643.25	110.44	61.25	0.00	0.00	61.25	100.00	17.35	832.29
13	Break	0:45:00
14	£15.50	STANDARD
15	£23.25	O/Time
16	£1.40	Bonus
17	£0.17	Fuel
18
19	1
20	2
21	3
22	4
23	5
24	6
25	7
26	8
27	9
28	10
29
30
31
32
050224 (2)

Cell Formulas
Range	Formula
I3:I7	I3	=E3-F3-H3-M3-N3
J3:J7	J3	=I3+H3
K3:K7	K3	=E3-F3-H3
L3:L7	L3	=G3-J3
M3:M7	M3	=E3-G3
O3:O7	O3	=(J3*24)*A$14
P3:P7	P3	=(L3*24)*A$15
Q3:Q7	Q3	=(K3*24)*A$16
S3:S7	S3	=R3*A$16
T3:T7	T3	=Q3-S3
V3:V7	V3	=U3*A$17
W3:W7	W3	=O3+P3+T3+V3
E3:E7	E3	=D3-C3
G3:G7	G3	=E3-(F3)
B4:B9	B4	=B3+1
E11:N11	E11	=SUM(E3:E9)
E12:N12	E12	=CONCATENATE(24*DAY(E11)+HOUR(E11),".",MINUTE(E11))
O12:V12	O12	=SUM(O3:O7)
W12	W12	=SUM(W3:W9)

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

Cells with Data Validation
Cell	Allow	Criteria
R3:R10	List	=$A$19:$A$28

Went back to the drawing board to simplify things so now it works.. But always a but.....
Needs tidying up if cell c3:d9 have no input in them, then i get various errors in other cells I know you can use iferror to cover this but cant seem to work out how can anyone assist

 

[awoohaw]

@HappyChappy
When I copied your data over and typed data in C8:D9 I did not see and calculation errors.
What cells have formulas that are failing? (I don't see these in your xl2bb mini sheet either).

 

[HappyChappy]

if you remove say monday start and finish times then you will see the errors on that row appear

 

[awoohaw]

if you remove say monday start and finish times then you will see the errors on that row appear

OK. Column G,I,J,K involve date/time subtractions and excel doesn't like going negative in date/time calculations (just my way of saying it, i'm sure there is a more accurate way). But, it isn't a typical formula error like #VALUE, #NA, #DIV/0. You just need to trap the situation that causes the calculation to happen.
So something like this:

=IF(OR(C3=0,D3=0),0,YourFormula)

 

[HappyChappy]

OK. Column G,I,J,K involve date/time subtractions and excel doesn't like going negative in date/time calculations (just my way of saying it, i'm sure there is a more accurate way). But, it isn't a typical formula error like #VALUE, #NA, #DIV/0. You just need to trap the situation that causes the calculation to happen.
So something like this:

=IF(OR(C3=0,D3=0),0,YourFormula)

thank-you for the assist