| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating Time difference based on a condition | Excel Discussion (Misc queries) | |||
| date/time stamp | Excel Worksheet Functions | |||
| calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
| AGE CALCULATING EXCEL SPREADSHEETS AND OTHERS! | Excel Discussion (Misc queries) | |||
| Calculating date/time | Excel Discussion (Misc queries) | |||
Hybrid Mode
