Hi
My formula assumes, there are no empty entries - off-work days are skipped.
P.e.
A2="???", B2=5/1/08, C2="Shift1", D2=17:30, E2=05:30
A3="???", B2=5/2/08, C2="Shift2", D2=17:30, E2=05:30
A3="???", B2=5/13/08, C2="Shift3", D2=05:30, E2=17:30
....
Having empty entries isn't a good idea - it makes all your formulas much
more complex, and your workbook slower. And I think it is possible to
construct a formula, which calculates previous off-time (because then we
have a fixed base point - the 1st entry in table), calculating max previous
date, then finding it's row number, and then using INDEX function to locate
last ending time (and now think about including such subformula instead
every part of my formula), but I can't see a way do same with next off-time
without writing an UDF.
My advice: reconsider your design.
--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )
"Rusty" <(E-Mail Removed)> wrote in message
news:A34CB413-CEE3-47ED-AAA5-(E-Mail Removed)...
> Thanks for the reply. This works very well for most days. However, I've
> come
> across a few situations where this doesn't completely work.
>
> For instance, say the End time falls into cell E13 (05:30 on 3/2/08) and
> the
> next start time doesn't begin until cell D18 (05:30 on 3/7/08). The
> duration
> for time OFF returns as 29:30 because it doesn't factor the the other
> cells
> with no value (being other 24 hour periods of non-work time -- in cells
> e14,
> e15, e16 & e17(which the last (e17) I believe does factor into your
> equation)).
>
> Is there a way to modify your formula to account for all these non work
> days
> (blank cells in the E column) until it reaches one with an end time? In
> the
> above example I believe it should have returned a total of 101:30 hours
> off.
> But there are other situations where the off time could be less than 24
> hours
> or up to 14 days (336:00 + StartTime) until the next StartTime.
>
> Thanks so much for the help.
>
>
> "Arvi Laanemets" wrote:
>
>> Hi
>>
>> For worked hours:
>> =IF(OR(D2="",E2=""),"",E2-D2+(E2<D2))
>> For off hours:
>> =IF(OR(B3="",D3=""),"",(B3-B2+D3-(E2<D2))-E2)
>>
>> Format columns with both formulas as Custom "[hh]:mm"
>>
>>
>> --
>> Arvi Laanemets
>> ( My real mail address: arvi.laanemets<at>tarkon.ee )
>>
>>
>> "Rusty" <(E-Mail Removed)> wrote in message
>> news:0B1EB115-D1A5-4465-BBDD-(E-Mail Removed)...
>> > What I need to calculate is the duration, in [hh]:mm between the end
>> > time
>> > of
>> > a shift on one day and the start time of the next shift on another day.
>> > Where
>> > this gets complicated, the next start time could be up to 14 days after
>> > the
>> > end of the previous shift. How can I write a function to calculate
>> > these
>> > total hours?
>> >
>> > Ex: Shift 1: 5/1/08 - 17:30 to 05:30 next day
>> > Shift 2: 5/2/08 - 17:30 to 05:30 next day (returns 12:00 for the
>> > duration between shifts)
>> > Shift 3: 5/13/08 05:30 to 17:30 (how do I automatically calculate
>> > time
>> > between 5/3/08 05:30 and 5/13/08 05:30 and have this same function
>> > calculate
>> > time between 5/2/08 05:30 and 5/2/08 17:30?)
>> >
>> > My columns are A(day); B(date); C(Shift [i.e. assigned working shift]);
>> > D(Start Time); E(End Time); F(Duration of Hours Worked); G(Duration of
>> > Hours
>> > OFF)
>> >
>> > Every day/date is a new row and none are skipped.
>>
>>
>>
Similar Threads
