Date Calculation

[TMCEWAN259]

Okay I thought I had this thing licked but a new wrinkle has come up. In my original request I outlined that I was trying to calculate a billing issue based upon the number of hours someone was housed.

A5= 1/1/2009 23:50 (Time Arrived)
C5= 1/3/2009 12:10 (Time Left)
F5 = Total Hours
(Formula behind F5) =(+C5-A5)*24*60/60
F5 is formatted as a general number
A5 and C5 are formatted as custom m/d/yyyy h:mm

In the example above F5 is 36. So now the folks using this spreadsheet tell me that they need it to display the hours AND minutes - because it impacts billing.

H5 currently gives the billing amount based upon this formula
=IF(F5<=8,55,IF(F5<=24,120,185+(65*INT((F5-24)/24))))

So if they are here for exactly 48 hrs it is $185 but if they are here for 48 hours and 1 minute it goes to $250.

[shg]

If you just want the result rounded up to whole hour, then in F5,

=ceiling(24*(c5-a5), 1)

[NBVC]

Not sure where the problem is? Is it the result of $250 is wrong or is it that you need cell F5 to display hours and minutes? Please clarify.

[TMCEWAN259]

Yeah sorry about that - I do need it to display hours and minutes so they can verify the billing amount.

[mudraker]

To calculate & display h:mm in f5 change formatting to [h]:mm
change formula to =C5-A5

[TMCEWAN259]

mudraker - okay I did that and indeed it does change the results in F5 to 36:11 - however now it totally throws the calculation in H5 out of whack. Prior to the change you suggest it shows up as the correct amount - $185 - after your change it goes to $55?

[NBVC]

Now multiply each of the F5's in the H5 formula by 24:

=IF(F5*24<=8,55,IF(F5*24<=24,120,185+(65*INT((F5*24-24)/24))))

[TMCEWAN259]

NBVC - Mudraker - Thank you - will marked as solved - I have no mind for math or functions, thankfully others do. Much appreciated.