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)

--
Regards,
Fred


"relaing" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[relaing]

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

[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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[relaing]

Bob
Thanks!! That works beautifully!
Rick