# Time computation



## Franco88 (Dec 27, 2022)

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

Book2ABCD1TIME INTIME OUTDURATION HOURSCHARGE213:5422:549:0051.625314:5423:549:0051.625415:540:549:0051.625516:541:549:0051.625Sheet1Cell FormulasRangeFormulaC2:C5C2=B2-A2D2:D5D2=IF(C2<=TIME(3,0,0)*50,50+(C2+TIME(3,0,0)*10))


----------



## Dave Patton (Dec 27, 2022)

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

A guess follows

Time.xlsmABCD1TIME INTIME OUTDURATION HOURSCHARGE213:54:0022:54:009.00110314:54:0016:54:002.00413:54:000:54:0011.00516:54:5916:54:5824.00460611cCell FormulasRangeFormulaD2D2=(C2>3)*50+(C2-3)*10+(C5=24)C2:C5C2=(B2-A2+(A2>B2))*24D5D5=(C5>3)*50+(CEILING(C5,0.05)-3)*10+(CEILING(C5,0.05)=24)*200


----------



## Franco88 (Dec 27, 2022)

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

Book1ABCDEFGHIJ1TIME INTIME OUTDURATION HOURSEXPECTED RESULTSTotal HoursInitial FeeOvernight every 24 hoursTOTAL212/28/2022 13:5412/28/2022 22:549.00110.0015050312/28/2022 15:5812/28/2022 16:591.0250.00250412/28/2022 16:5412/28/2022 19:543.0050.00350512/28/2022 20:2312/28/2022 23:593.6060.0046060612/28/2022 11:0012/28/2022 23:5912.98150.0057070712/28/2022 13:5812/29/2022 13:5824.00460.0068080812/28/2022 13:5412/29/2022 13:5524.02470.0079090981001001091101101110120120121113013013121401401413150150151416016016151701701716180180181719019019182002002019210210212022022022212302302322240240242325025025242602004602625270200470Sheet1Cell FormulasRangeFormulaC2:C8C2=(B2-A2+(A2>B2))*24


----------



## HongRu (Dec 28, 2022)

How about this?
D2=

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


----------



## Dave Patton (Dec 28, 2022)

Time.xlsmABCD1TIME INTIME OUTDURATION HOURSEXPECTED RESULTS213:5422:549110315:5816:59150416:5419:54350520:2323:59460611:0023:5913150713:5813:5824460828-12-22 13:5829-12-22 14:5825470911cCell FormulasRangeFormulaC2:C7C2=ROUND((B2-A2+(A2>B2))*24,0)D2:D8D2=(C2>0)*50+(C2>3)*(C2-3)*10+(C2>=24)*200C8C8=ROUND((B8-A8)*24,0)


----------



## kvsrinivasamurthy (Dec 28, 2022)

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 (Dec 29, 2022)

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.xlsmABCD1TIME INTIME OUTDURATION HOURSEXPECTED RESULTS228-Dec-22 13:5428-Dec-22 22:549110328-Dec-22 15:5828-Dec-22 16:59150428-Dec-22 16:5428-Dec-22 19:54350528-Dec-22 20:2328-Dec-22 23:59460628-Dec-22 11:0028-Dec-22 23:5913150728-Dec-22 13:5829-Dec-22 13:5824460828-Dec-22 13:5429-Dec-22 14:5525470911ccCell FormulasRangeFormulaC2:C8C2=ROUND((B2-A2)*24,0)D2:D8D2=(C2>0)*50+(C2>3)*(C2-3)*10+(C2>=24)*200


----------



## Franco88 (Dec 30, 2022)

Dave Patton said:


> 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.
> ...




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 (Jan 2, 2023)

See Post #6.
It gives expected result.


----------

