Output Number of Days and Hours

[limecity]

Trying to do a excel formula to count number of days and hours so i can calculate the total charge for car rental.

Cell C2: $100.00
Cell F2: 23/3/2011 10:00:00 AM
Cell G2: 25/3/2011 11:00:00 AM

I set =G2-F2
Outputs : 2:01:00 with the format of d:hh:mm
or it shows 2 1/24 in other format
or it shows 2.04 in another format

I did a multiply of OUTPUT TIME DURATION * RATE
= $100.00 * 2.04
= $204.17

My expectation was to get the number to 2.1
(2 days and 1 hour)
so, i can do the multiple of

$100.00 * 2.1 = $210.00


Question 1:
How to get it to 2.1 instead of 2.04

---------------------------------

Then if it exceeds 5 hours, it will consider as 1 full day
meaning, 3.0

Question 2:
How to set it to +1 for the number of day when it exceeds 5 hours ?


Rather complicated issue I got here. Can the formula do this or i need to do some programming in macro?

Refer to the attachment for the excel file. Thank you in advance for the help if there is any contributors.

[daddylonglegs]

so if the time period is 2 days and 4 hours you want to charge 240.00 but if it's 2 days and 5 hours you'd charge as for 3 days i.e. 300.00, what about partial hours, waht would you charge for 2 days and 3.5 hours?

[limecity]

Thank you very much for the quick reply.

the customer will be allow to select time like
2pm
2.30pm
3.pm
3.30pm.
etc etc.

So if its 2 days 2 hours and 30 mins
the 30 mins will not be counted and waived.

tried google for what I wanted but can't find anything similiar that i can mod.

[daddylonglegs]

OK, try this formula in I2

=(INT(H2)+IF(HOUR(H2)<5,HOUR(H2),10)/10)*C2

[limecity]

hey thanks man!

didn't know IF and > can be used in the formula box.
thats something new for me.

works perfectly.
appreciate it.