calculating date/time
 

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


--
relaing
------------------------------------------------------------------------
relaing's Profile: http://www.excelforum.com/member.php...o&userid=29355
View this thread: http://www.excelforum.com/showthread...hreadid=490647

calculating date/time
 

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)

--
Regards,
Fred


"relaing" wrote in
message ...

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


--
relaing
------------------------------------------------------------------------
relaing's Profile:
http://www.excelforum.com/member.php...o&userid=29355
View this thread: http://www.excelforum.com/showthread...hreadid=490647

calculating date/time
 

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

This is what I came up with (I am NOT proficient at Excel so it may be
rather clunky ). The problem with this, is it does not give me a minimum
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 chargeable
hour rate of 2.2. What I am getting with this is 2.1; with a chargeable
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

Rick


--
relaing
------------------------------------------------------------------------
relaing's Profile: http://www.excelforum.com/member.php...o&userid=29355
View this thread: http://www.excelforum.com/showthread...hreadid=490647

calculating date/time
 

Try this version

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

--

HTH

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


"relaing" wrote in
message ...

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

This is what I came up with (I am NOT proficient at Excel so it may be
rather clunky ). The problem with this, is it does not give me a minimum
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 chargeable
hour rate of 2.2. What I am getting with this is 2.1; with a chargeable
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

Rick


--
relaing
------------------------------------------------------------------------
relaing's Profile:
http://www.excelforum.com/member.php...o&userid=29355
View this thread: http://www.excelforum.com/showthread...hreadid=490647

calculating date/time
 

Bob
Thanks!! That works beautifully!
Rick


--
relaing
------------------------------------------------------------------------
relaing's Profile: http://www.excelforum.com/member.php...o&userid=29355
View this thread: http://www.excelforum.com/showthread...hreadid=490647