Calculate the duration between the end time and start time of anot

[Rusty]

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.

 

[Arvi Laanemets]

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"

 

[Rusty]

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.

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 )

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.

 

[Rusty]

I can send you a copy of the worksheet I'm working on if that would be more
helpful.

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.

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 )

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.

 

[Arvi Laanemets]

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 )

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.

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 )

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.