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

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

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

(E-Mail Removed)
(E-Mail Removed) with @tiscali.co.uk


"dps9460" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>
> 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
>

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

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

(E-Mail Removed)
(E-Mail Removed) with @tiscali.co.uk


"Ron Coderre" <(E-Mail Removed)>
wrote in message
news:(E-Mail Removed)...
>
> 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
>

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

(E-Mail Removed)
(E-Mail Removed) with @tiscali.co.uk


"Sandy Mann" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> (E-Mail Removed)
> (E-Mail Removed) with @tiscali.co.uk
>
>
> "dps9460" <(E-Mail Removed)> wrote in
> message news:(E-Mail Removed)...
>>
>> 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
>>
>
>

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

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:C2>0)*ROW(C$2:C2))>=MAX((B$2:B2>0)*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: 2
hours
If both cells are blank and the last departure HAS returned: Zer
hours

Does that help?

Regards,
Ro

--
Ron Coderr
-----------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...fo&userid=2141
View this thread: http://www.excelforum.com/showthread.php?threadid=54805

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

(E-Mail Removed)
(E-Mail Removed) with @tiscali.co.uk


"Ron Coderre" <(E-Mail Removed)>
wrote in message
news:(E-Mail Removed)...
>
> 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:C2>0)*ROW(C$2:C2))>=MAX((B$2:B2>0)*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
>

True, Sandy....I failed to mention that the formula I posted is an ARRA
FORMULA*.

Your previously posted formula always calculates 24 hours when bot
cells are blank.

I believe this formula satisfies the OP's requirements:
=IF(AND(ISBLANK(C2),MAX((C$2:C2>0)*ROW(C$2:C2))>=MAX((B$2:B2>0)*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 pres
[Enter], instead of just pressing [Enter]

--
Ron Coderr
-----------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...fo&userid=2141
View this thread: http://www.excelforum.com/showthread.php?threadid=54805