I want to calculate the number of hours that our water plant operates. During
the course of a day we may start up and shut down a couple of times. I have
made two columns one for start time and one for shut down.I also have a
column that has the time so the operators then enter in the start time next
to this column. Any suggestions would help how to calculate plant hours-this
is running time. This case would be 9 hours of actual run time

Time Start Sut Down
0000
0100
0200 2:00
0300
0400
0500
0600 6:30
0700
0800
0900
1000 10:00
1100
1200
1300
1400
1500 3:00

Well, I'd scrap what you have and start over. The logbook format that
you have is suitable for operators and managers to write, for example,
the status of the plant and equipment at hourly intervals, but it does
not suit what you're trying to do.

Instead I'd make up a sheet with 6 columns, as:
A. Date
B. Start Time
C. Stop Time
D. This Run
E. Daily Running Time
F. Total Run Time by Day

Columns A through C should be obvious. I'd format the first column for
"date" however you want that to appear. Columns B & C I'd format for
however you want to enter your "o'clock" times, and D-E-F I'd format
for "h:mm" to show hours and minutes of operation.

Formula in column D:D is (at D2, and copied down however many rows):
=IF( AND( ISNUMBER( C2), ISNUMBER( B2)), C2 - B2, IF( ISNUMBER( B2),
NOW() - B2, ""))

Formula in column E:E is (also at E2, and copied downward):
=IF( ISNUMBER( D2), IF( A2 = A1, D2 + E1, D2), "")

Formula in column F:F is (same method):
=IF( A2 = A3, "", E2)

You can hide column E:E. You need to have it compute, but you don't
need to display it.

This does not have error-trapping (start times after stop times, dates
in incorrect sequence, etc.), so it's not a real "application", but you
can develop that stuff. Your sheet arrangement above is entirely
unsuitable, but an arrangement like this will let you show plant
operations for months and months ... on a single worksheet.

And your actual hours of operation in your example are 9:30, not 9:00.

Chris

Chris
The formula works but what happens if the plant runs a full 24 hours in one
day I am not sure how to enter the time start and shut down. I do not get
hours run when entering in start at midnight and shut down at midnight.
thanks

"Blue Hornet" wrote:

Well, I'd scrap what you have and start over. The logbook format that
you have is suitable for operators and managers to write, for example,
the status of the plant and equipment at hourly intervals, but it does
not suit what you're trying to do.

Instead I'd make up a sheet with 6 columns, as:
A. Date
B. Start Time
C. Stop Time
D. This Run
E. Daily Running Time
F. Total Run Time by Day

Columns A through C should be obvious. I'd format the first column for
"date" however you want that to appear. Columns B & C I'd format for
however you want to enter your "o'clock" times, and D-E-F I'd format
for "h:mm" to show hours and minutes of operation.

Formula in column D:D is (at D2, and copied down however many rows):
=IF( AND( ISNUMBER( C2), ISNUMBER( B2)), C2 - B2, IF( ISNUMBER( B2),
NOW() - B2, ""))

Formula in column E:E is (also at E2, and copied downward):
=IF( ISNUMBER( D2), IF( A2 = A1, D2 + E1, D2), "")

Formula in column F:F is (same method):
=IF( A2 = A3, "", E2)

You can hide column E:E. You need to have it compute, but you don't
need to display it.

This does not have error-trapping (start times after stop times, dates
in incorrect sequence, etc.), so it's not a real "application", but you
can develop that stuff. Your sheet arrangement above is entirely
unsuitable, but an arrangement like this will let you show plant
operations for months and months ... on a single worksheet.

And your actual hours of operation in your example are 9:30, not 9:00.

Chris

scott45 wrote:
Chris
The formula works but what happens if the plant runs a full 24 hours in one
day I am not sure how to enter the time start and shut down. I do not get
hours run when entering in start at midnight and shut down at midnight.
thanks


Scott

Okay, I imagined you'd get around to this ... lol.

Set up your columns so that your Start and Stop entries contain both
Date AND Time elements. So, for example, your column A values might be
entered like:
Start Date & Time
8/1/05 8:00
8/1/05 10:00
8/1/05 12:00
8/2/05 15:00
8/3/05 17:00
8/3/05 21:00
8/3/05 22:45

Column B as:
Stop Date and Time
8/1/05 9:00
8/1/05 11:00
8/2/05 13:30
8/3/05 13:00
8/3/05 19:00
8/3/05 22:40
8/3/05 23:05

Column C "This Run" formula is now:
=IF( AND( ISNUMBER( A2), ISNUMBER( B2)), B2 - A2, IF( ISNUMBER(
A2),NOW() - A2, ""))
(Entered at C2 and copied downward)

Column D "Daily Run" is:
=IF( AND( ISNUMBER( A2), ISNUMBER( B2), DAY( A2) = DAY( B2)), B2 - A2,
"")

Column E "Total Run Time by Day" is:
=IF( AND( ISNUMBER( A3), ISNUMBER( A2), DAY( A2) = DAY( B3)), "", D2)

If you use these entries as I have shown, you'll get values in column E
as:
Total Run Time by Day
[BLANK] [Still accumulating time for 8/1/5]
0:2:00 [Showing two hours run on 8/1/5]
1:3:30 [Showing one day, three and one-half hours, from 8/1/5 to
8/2/5]
0:22:00 [Showing twenty-two hours run from 8/2/5 to 8/3/5]
[BLANK] [Still accumulating time for 8/3/5]
[BLANK] [Still accumulating time for 8/3/5]
0:4:00 [Showing four hours run on 8/3/5 -- doesn't include any part of
the 22 hours above -- oh, well.]

Formatting in E is set to show "d:h:mm", which I believe is a standard
"Custom" format (isn't that an oxymoron?).

I hope this has given you ideas to work with; I really don't have more
time to design the sheet for you.

Chris

Chris

I was not entering time in correctly in first two columns but now it does
not add up the two times.
Scott

"scott45" wrote:

I want to calculate the number of hours that our water plant operates. During
the course of a day we may start up and shut down a couple of times. I have
made two columns one for start time and one for shut down.I also have a
column that has the time so the operators then enter in the start time next
to this column. Any suggestions would help how to calculate plant hours-this
is running time. This case would be 9 hours of actual run time

Time Start Sut Down
0000
0100
0200 2:00
0300
0400
0500
0600 6:30
0700
0800
0900
1000 10:00
1100
1200
1300
1400
1500 3:00

I am starting to understand all this but if this matters everyday westart a
new bench sheet to record our times run. We do not carry over to the next day
only figure 24 hours if the plant does not shut down. And then start the next
day. These hours are used to figue up chemical feed rates.

"scott45" wrote:

Chris

I was not entering time in correctly in first two columns but now it does
not add up the two times.
Scott

"scott45" wrote:

I want to calculate the number of hours that our water plant operates. During
the course of a day we may start up and shut down a couple of times. I have
made two columns one for start time and one for shut down.I also have a
column that has the time so the operators then enter in the start time next
to this column. Any suggestions would help how to calculate plant hours-this
is running time. This case would be 9 hours of actual run time

Time Start Sut Down
0000
0100
0200 2:00
0300
0400
0500
0600 6:30
0700
0800
0900
1000 10:00
1100
1200
1300
1400
1500 3:00

Blue Hornet
Yes this is very helpful thank you very much but I am getting #value
returned in Daily Run Time and Total run time. I have checked and rechecked
formulas
Scott

"scott45" wrote:

I want to calculate the number of hours that our water plant operates. During
the course of a day we may start up and shut down a couple of times. I have
made two columns one for start time and one for shut down.I also have a
column that has the time so the operators then enter in the start time next
to this column. Any suggestions would help how to calculate plant hours-this
is running time. This case would be 9 hours of actual run time

Time Start Sut Down
0000
0100
0200 2:00
0300
0400
0500
0600 6:30
0700
0800
0900
1000 10:00
1100
1200
1300
1400
1500 3:00