Calculation Total Time With Dates/Times That Overlaps

  • Thread starter Thread starter Steve Gilley
  • Start date Start date
S

Steve Gilley

I have four columns: StartDate, StartTime, EndDate, and EndTime. Thi
represents the timed duration of several washing machines used in
continuing fashion. I want to calculate the total time the washers ar
in use so I can figure out when they were not in use. This eludes m
because some of the dates/times overlap. Can someone help with m
calculate to total in use time?

ColumnA ColumnB ColumnC ColumnD
StartDate EndDate EndDate EndTime
1/21/2008 10:00 1/22/2008 9:30
1/21/2008 10:20 1/31/2008 10:30
1/23/2008 17:00 1/25/2008 9:30
1/23/2008 17:05 1/28/2008 9:15
1/24/2008 12:00 1/24/2008 13:00
1/25/2008 9:29 1/25/2008 11:52
1/25/2008 15:00 1/29/2008 6:30
1/25/2008 15:15 1/25/2008 16:30
1/30/2008 12:15 2/1/2008 16:45
1/31/2008 16:15 1/31/2008 17:30
2/4/2008 14:30 2/5/2008 12:45
2/4/2008 14:30 2/4/2008 15:45
2/5/2008 12:30 2/7/2008 13:15
2/8/2008 12:15 2/8/2008 12:50
2/11/2008 8:00 2/11/2008 13:10
2/11/2008 13:30 2/11/2008 14:3
 
Can someone help with me calculate to total in use time?

ColumnA ColumnB ColumnC ColumnD
StartDate EndDate EndDate EndTime
1/21/2008 10:00 1/22/2008 9:30
Click to expand...

In E2 and copy down through E17 and more.

=C2+D2-A2-B2

Format as Custom [h]:mm or d:hh:mm, depending on which you want.

To avoid nonsensical results when some columns or rows are incomplete:

=if(A2*B2*C2*D2=0, "", C2+D2-A2-B2)

This works because dates are stored as number of days (since
12/31/1899), and time is stored as fractions of a day.
 
I don't think that addresses the question of overlap? My interpretation of
the OP's question was that the required answer was the total time that at
least one of the machines was running.
Maybe the clearest way to see what's going on is to have the start time of
any extra time in that session in column E, and the the end time of any
extra time in that session in column F. The extra time (if any) added that
session would be in column G. Use the following formulae:
For E2: =MAX(A2+B2,F1) and format as m/dd/yyyy h:mm
For F2: =MAX(C2+D2,F1) and format as above
For G2: =F2-E2 and format this, and the total of the column, as [h]:mm
You could get away without displaying all the intermediate calculations, but
it makes it clearer what the calculation is doing.

Looking at it the other way, the OP mentions wanting to know when the
machines are *not* in use, and the duration of any such gap can be seen from
a formula in H3 saying
=MAX(E3-F2,0)
Comfortingly, the total from column G and column H add together to the total
duration.
--
David Biddulph

Can someone help with me calculate to total in use time?

ColumnA ColumnB ColumnC ColumnD
StartDate EndDate EndDate EndTime
1/21/2008 10:00 1/22/2008 9:30
Click to expand...

In E2 and copy down through E17 and more.

=C2+D2-A2-B2

Format as Custom [h]:mm or d:hh:mm, depending on which you want.

To avoid nonsensical results when some columns or rows are incomplete:

=if(A2*B2*C2*D2=0, "", C2+D2-A2-B2)

This works because dates are stored as number of days (since
12/31/1899), and time is stored as fractions of a day.
 
Is that answer supposed to be the time when at least one machine was in use,
or when none were? My formula, see an earlier post, gave 348:15 and 160:15
respectively for those answers.
It wasn't obvious to me what your sheet was trying to do, nor what was
happening when the resolution was being changed. It didn't seem to converge
to a stable solution.
Hopefully the OP will be able to understand what each of us has done, and
whether either meets what he was trying to achieve.
 
Back
Top