Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
relaing
 
Posts: n/a
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.misc
Fred Smith
 
Posts: n/a
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.misc
relaing
 
Posts: n/a
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default 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



  #5   Report Post  
Posted to microsoft.public.excel.misc
relaing
 
Posts: n/a
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating Time difference based on a condition sonnethg Excel Discussion (Misc queries) 4 October 11th 05 09:25 PM
date/time stamp Jan Excel Worksheet Functions 7 July 14th 05 01:04 PM
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) RICHARD Excel Worksheet Functions 1 March 15th 05 06:49 PM
AGE CALCULATING EXCEL SPREADSHEETS AND OTHERS! RICHARD Excel Discussion (Misc queries) 0 March 1st 05 02:53 PM
Calculating date/time Paul Martin Excel Discussion (Misc queries) 1 December 7th 04 01:17 AM


All times are GMT +1. The time now is 11:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"