Time computation

[Franco88]

Need assistance to troubleshoot calculating beetween times in columns a1 and b1.


First 3 hours 50
succeeding 10 thereafter (after 3 hours)
and 200 for every 24 hours period

Thank you

Book2
	A	B	C	D
1	TIME IN	TIME OUT	DURATION HOURS	CHARGE
2	13:54	22:54	9:00	51.625
3	14:54	23:54	9:00	51.625
4	15:54	0:54	9:00	51.625
5	16:54	1:54	9:00	51.625
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=B2-A2
D2:D5	D2	=IF(C2<=TIME(3,0,0)*50,50+(C2+TIME(3,0,0)*10))

 

[Dave Patton]

Can you show the calculations and expected results for a variety of times?

A guess follows

Time.xlsm
	A	B	C	D
1	TIME IN	TIME OUT	DURATION HOURS	CHARGE
2	13:54:00	22:54:00	9.00	110
3	14:54:00	16:54:00	2.00
4	13:54:00	0:54:00	11.00
5	16:54:59	16:54:58	24.00	460
6
11c

Cell Formulas
Range	Formula
D2	D2	=(C2>3)*50+(C2-3)*10+(C5=24)
C2:C5	C2	=(B2-A2+(A2>B2))*24
D5	D5	=(C5>3)*50+(CEILING(C5,0.05)-3)*10+(CEILING(C5,0.05)=24)*200

 

[Franco88]

Wow, thank you for your prompt response, Kindly see the sample below

Book1
	A	B	C	D	E	F	G	H	I	J
1	TIME IN	TIME OUT	DURATION HOURS	EXPECTED RESULTS			Total Hours	Initial Fee	Overnight every 24 hours	TOTAL
2	12/28/2022 13:54	12/28/2022 22:54	9.00	110.00			1	50		50
3	12/28/2022 15:58	12/28/2022 16:59	1.02	50.00			2			50
4	12/28/2022 16:54	12/28/2022 19:54	3.00	50.00			3			50
5	12/28/2022 20:23	12/28/2022 23:59	3.60	60.00			4	60		60
6	12/28/2022 11:00	12/28/2022 23:59	12.98	150.00			5	70		70
7	12/28/2022 13:58	12/29/2022 13:58	24.00	460.00			6	80		80
8	12/28/2022 13:54	12/29/2022 13:55	24.02	470.00			7	90		90
9							8	100		100
10							9	110		110
11							10	120		120
12							11	130		130
13							12	140		140
14							13	150		150
15							14	160		160
16							15	170		170
17							16	180		180
18							17	190		190
19							18	200		200
20							19	210		210
21							20	220		220
22							21	230		230
23							22	240		240
24							23	250		250
25							24	260	200	460
26							25	270	200	470
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=(B2-A2+(A2>B2))*24

 

[HongRu]

How about this?
D2=

=VLOOKUP(ROUNDUP(C2,0),$G$2:$J$26,4,1)

 

[Dave Patton]

Time.xlsm
	A	B	C	D
1	TIME IN	TIME OUT	DURATION HOURS	EXPECTED RESULTS
2	13:54	22:54	9	110
3	15:58	16:59	1	50
4	16:54	19:54	3	50
5	20:23	23:59	4	60
6	11:00	23:59	13	150
7	13:58	13:58	24	460
8	28-12-22 13:58	29-12-22 14:58	25	470
9
11c

Cell Formulas
Range	Formula
C2:C7	C2	=ROUND((B2-A2+(A2>B2))*24,0)
D2:D8	D2	=(C2>0)*50+(C2>3)*(C2-3)*10+(C2>=24)*200
C8	C8	=ROUND((B8-A8)*24,0)

 

[kvsrinivasamurthy]

In D2

=IF(C2>0,IF(C2<=3,50,IF(C2<=23,50+10*CEILING(C2-3,1),250+10*CEILING(C2-3,1))),"")

 

[Dave Patton]

N.B.
The first post had just time; the later post had both date and time.
I changed the time on cell B8.
Use the rounding or ceiling formula that is correct for your requirements.

Time.xlsm
	A	B	C	D
1	TIME IN	TIME OUT	DURATION HOURS	EXPECTED RESULTS
2	28-Dec-22 13:54	28-Dec-22 22:54	9	110
3	28-Dec-22 15:58	28-Dec-22 16:59	1	50
4	28-Dec-22 16:54	28-Dec-22 19:54	3	50
5	28-Dec-22 20:23	28-Dec-22 23:59	4	60
6	28-Dec-22 11:00	28-Dec-22 23:59	13	150
7	28-Dec-22 13:58	29-Dec-22 13:58	24	460
8	28-Dec-22 13:54	29-Dec-22 14:55	25	470
9
11cc

Cell Formulas
Range	Formula
C2:C8	C2	=ROUND((B2-A2)*24,0)
D2:D8	D2	=(C2>0)*50+(C2>3)*(C2-3)*10+(C2>=24)*200

 

[Franco88]

N.B.
The first post had just time; the later post had both date and time.
I changed the time on cell B8.
Use the rounding or ceiling formula that is correct for your requirements.

Time.xlsm
	A	B	C	D
1	TIME IN	TIME OUT	DURATION HOURS	EXPECTED RESULTS
2	28-Dec-22 13:54	28-Dec-22 22:54	9	110
3	28-Dec-22 15:58	28-Dec-22 16:59	1	50
4	28-Dec-22 16:54	28-Dec-22 19:54	3	50
5	28-Dec-22 20:23	28-Dec-22 23:59	4	60
6	28-Dec-22 11:00	28-Dec-22 23:59	13	150
7	28-Dec-22 13:58	29-Dec-22 13:58	24	460
8	28-Dec-22 13:54	29-Dec-22 14:55	25	470
9
11cc

Cell Formulas
Range	Formula
C2:C8	C2	=ROUND((B2-A2)*24,0)
D2:D8	D2	=(C2>0)*50+(C2>3)*(C2-3)*10+(C2>=24)*200

Thank you so much.
One more thing to test is how to adapt a formula that takes 1 minute to change into an hour (fraction thereof)

e.g.
3.02 = 4 hrs
4.01= 5 hrs
24.01 = 25 hrs

 

[kvsrinivasamurthy]

See Post #6.
It gives expected result.