calculating date/time

[relaing]

Hope someone is able to help.
This is to calculate the chargeable hours for a SAR boat in the water

Date In| Time In| Date Out| Time Out|Conversion Time| Total

Conversion time is value of ((DateOut+TimeOut)-(DateIn+TimeIn)*24) but
needs to be rounded up to the nearest 0.1 i.e. 0-6=.1, 7-12=.2,
13-18=.3 etc. with a minimum chargeable time of 2 hours.

Have tried ROUNDUP and CEILING but obviously not the correct way!

Thanks
Rick

 

[Fred Smith]

I gather when you say "0-6=.1" you mean 0-6 *minutes*. And, I assume the boat
came in after it went out. If so, the following should work:

=min(roundup((datein+timein-dateout-timeout)*24,1),2)

 

[relaing]

Fred
Thanks for the reply. You are correct - 0-6 is the minutes and th
TimeIn DateIn is when the boat went in the water and TimeOut DateOut i
when it came out of the water.
I entered your formula, but it wouldn't calculate the conversion highe
than 2.0

This is what I came up with (I am NOT proficient at Excel so it may b
rather clunky ). The problem with this, is it does not give me a minimu
of 2 hours, and it does not round up the way I need.
B74=DateInWater
J74=DateOffWater
N74=TimeOffWater
F74=TimeOnWater

When the total time on water =2hours 7minutes I should get a chargeabl
hour rate of 2.2. What I am getting with this is 2.1; with a chargeabl
rate of 2hours 13minutes I am getting 2.2 instead of 2.3.



=IF(B74="",0,MIN(ROUNDUP(((J74+N74)-(B74+F74))*24,(IF((J74+N74)-(B74+N74)*24<2,2,1)))))

Hope this makes sense
Thanks

Ric

 

[Bob Phillips]

Try this version

=MAX(ROUNDUP(((dateout+timeout)-(datein+timein))*24,1),2)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[relaing]

Bob
Thanks!! That works beautifully!
Rick