I have four columns: StartDate, StartTime, EndDate, and EndTime. This
represents the timed duration of several washing machines used in a
continuing fashion. I want to calculate the total time the washers are
in use so I can figure out when they were not in use. This eludes me
because some of the dates/times overlap. 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
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:30




--
Steve Gilley

On Feb 16, 2:01 pm, Steve Gilley wrote:
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

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.




On Feb 16, 2:01*pm, Steve Gilley <Steve.Gilley.
wrote:
I have four columns: StartDate, StartTime, EndDate, and EndTime. This
represents the timed duration of several washing machines used in a
continuing fashion. I want to calculate the total time the washers are
in use so I can figure out when they were not in use. This eludes me
because some of the dates/times overlap. 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
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:30

--
Steve Gilley

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

"joeu2004" wrote in message
...
On Feb 16, 2:01 pm, Steve Gilley wrote:
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

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.




On Feb 16, 2:01 pm, Steve Gilley <Steve.Gilley.
wrote:
I have four columns: StartDate, StartTime, EndDate, and EndTime. This
represents the timed duration of several washing machines used in a
continuing fashion. I want to calculate the total time the washers are
in use so I can figure out when they were not in use. This eludes me
because some of the dates/times overlap. 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
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:30

--
Steve Gilley

My answer: 159:48
http://www.freefilehosting.net/download/3c76e

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.
--
David Biddulph

"Herbert Seidenberg" wrote in message
...
My answer: 159:48
http://www.freefilehosting.net/download/3c76e