If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
A challenge, Serious help requested
I would like to show the HRS AWAY for each day when a departure and return time are shown. There are times when 2 departure/return times are shown in a one week period. I have not been able to show HRS AWAY for days that are more than 2 days apart or cover multiple departure/return times. I have an example below which shows how this is set up. I hope someone can help with this one. Thanks 4844 +-------------------------------------------------------------------+ |Filename: Travel Time.txt | |Download: http://www.excelforum.com/attachment.php?postid=4844 | +-------------------------------------------------------------------+ -- dps9460 ------------------------------------------------------------------------ dps9460's Profile: http://www.excelforum.com/member.php...o&userid=14725 View this thread: http://www.excelforum.com/showthread...hreadid=548056 |
#2
|
|||
|
|||
A challenge, Serious help requested
Hi 4844,
Your link to excelforum failed. To calc your time away you just need to subtract the time and dates as follows. A1 : Departure date and time B1 : Return date and time In C1 put the formula =(B1-A1)*24 and format the cell as general. HTH Martin |
#4
|
|||
|
|||
A challenge, Serious help requested
dps9460 Regarding your attached table (which isn't accessible on the Excel newsgroups, by the way): Day Dep Arr Hrs_away SUN 8:00 A 16 MON 24 TUE 24 WED 6:00 P 18 THUR 8:00 A 16 FRI 24 SAT 9:00 P 21 Try this.... With your data in A1:C8 D2: =IF(B2"",1-B2,IF(C2"",C2,1))*24 Copy that formula down as far as you need Does that help? Regards, Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=548056 |
#5
|
|||
|
|||
A challenge, Serious help requested
Ron that works as long as there is only one entry in that row. If there is
a Dep and Arr time for the same day then it is as if the Arr time was not there. -- Regards Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Ron Coderre" wrote in message ... dps9460 Regarding your attached table (which isn't accessible on the Excel newsgroups, by the way): Day Dep Arr Hrs_away SUN 8:00 A 16 MON 24 TUE 24 WED 6:00 P 18 THUR 8:00 A 16 FRI 24 SAT 9:00 P 21 Try this.... With your data in A1:C8 D2: =IF(B2"",1-B2,IF(C2"",C2,1))*24 Copy that formula down as far as you need Does that help? Regards, Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=548056 |
#6
|
|||
|
|||
A challenge, Serious help requested
If you have any days when you are not away then you would have to enter 12
AM in both the Departed & Returned columns, (or remove the formula) If you want the Hours Away to be time rather than decimal hours then use: =(IF(C2="",24,C2*24)-IF(B2="",0,B2*24))/24 and custom format the column as [h]:mm -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Sandy Mann" wrote in message ... With your table in A18 try: =IF(C2="",24,C2*24)-IF(B2="",0,B2*24) to return each day's away hours -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "dps9460" wrote in message ... I would like to show the HRS AWAY for each day when a departure and return time are shown. There are times when 2 departure/return times are shown in a one week period. I have not been able to show HRS AWAY for days that are more than 2 days apart or cover multiple departure/return times. I have an example below which shows how this is set up. I hope someone can help with this one. Thanks 4844 +-------------------------------------------------------------------+ |Filename: Travel Time.txt | |Download: http://www.excelforum.com/attachment.php?postid=4844 | +-------------------------------------------------------------------+ -- dps9460 ------------------------------------------------------------------------ dps9460's Profile: http://www.excelforum.com/member.php...o&userid=14725 View this thread: http://www.excelforum.com/showthread...hreadid=548056 |
#7
|
|||
|
|||
A challenge, Serious help requested
dps9460 Wrote: I would like to show the HRS AWAY for each day when a departure and return time are shown. There are times when 2 departure/return times are shown in a one week period. I have not been able to show HRS AWAY for days that are more than 2 days apart or cover multiple departure/return times. I have an example below which shows how this is set up. I hope someone can help with this one. Thanks 4844 thanks for the replies everyone But what if there is a day with no travel. No departure no arrival, there should not be any time in the time away column. I been working with some of the formulas you all suggested and they are close to what I'm looking for. Somehow the departure has to be associated with the arrival time and then fill in all the time away columns from the departure through the arrival time to include the days in between. Thanks -- dps9460 ------------------------------------------------------------------------ dps9460's Profile: http://www.excelforum.com/member.php...o&userid=14725 View this thread: http://www.excelforum.com/showthread...hreadid=548056 |
#8
|
|||
|
|||
A challenge, Serious help requested
dps9460: See if this gets you where you want to be. I wrapped Sandy's formula within this: D2: =IF(AND(ISBLANK(C2),MAX((C$2:C20)*ROW(C$2:C2))=M AX((B$2:B20)*ROW(B$2:B2))),0,IF(C2="",24,C2*24)-IF(B2="",0,B2*24)) Here's the logic If there's a departure time with no return time: Calc the time If there's a departure time and a return time: Calc the net time If there's a return time with no departure time: Calc the time If both cells are blank and the last departure has NOT returned: 24 hours If both cells are blank and the last departure HAS returned: Zero hours Does that help? Regards, Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=548056 |
#9
|
|||
|
|||
A challenge, Serious help requested
I follow your logic Ron but unfortunately it now returns 0 for all days when
there is no Departure or Return time. I would suggest the formula that I posted originally: =IF(C2="",24,C2*24)-IF(B2="",0,B2*24) in D2 and =IF(AND(COUNT($B$2:B3)=COUNT($C$2:C3),COUNT(B3:C3) =0),"",IF(C3="",24,C3*24)-IF(B3="",0,B3*24)) in D3 and copied down as far as necessary. -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Ron Coderre" wrote in message ... dps9460: See if this gets you where you want to be. I wrapped Sandy's formula within this: D2: =IF(AND(ISBLANK(C2),MAX((C$2:C20)*ROW(C$2:C2))=M AX((B$2:B20)*ROW(B$2:B2))),0,IF(C2="",24,C2*24)-IF(B2="",0,B2*24)) Here's the logic If there's a departure time with no return time: Calc the time If there's a departure time and a return time: Calc the net time If there's a return time with no departure time: Calc the time If both cells are blank and the last departure has NOT returned: 24 hours If both cells are blank and the last departure HAS returned: Zero hours Does that help? Regards, Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=548056 |
#10
|
|||
|
|||
A challenge, Serious help requested
True, Sandy....I failed to mention that the formula I posted is an ARRAY FORMULA*. Your previously posted formula always calculates 24 hours when both cells are blank. I believe this formula satisfies the OP's requirements: =IF(AND(ISBLANK(C2),MAX((C$2:C20)*ROW(C$2:C2))=M AX((B$2:B20)*ROW(B$2:B2))),0,IF(C2="",24,C2*24)-IF(B2="",0,B2*24)) That array formula only calculates times for the trip duration, ignoring blank cells where no trip is in progress. (thanks for spotting the mispost) Regards, Ron *Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=548056 |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
ANN: PowerPoint Heaven Updates - Maze Challenge Game | tohlz | Powerpoint | 1 | March 1st, 2006 05:49 PM |
Conform a total to a list of results? | xmaveric | General Discussion | 1 | August 21st, 2005 07:22 PM |
Conform a total to a list of results? | xmaveric | Worksheet Functions | 0 | August 21st, 2005 10:42 AM |
delivered/read receipt requested | marvquestion | General Discussion | 2 | May 16th, 2005 08:45 PM |
I have a task that was "requested by" someone. At Task window, c. | Engr2 | General Discussion | 0 | January 25th, 2005 03:45 PM |