Well, the starting value is still an issue, because it seems as though THAT
is a given, but can be subject to change. For instance, if it is OFF at the
end of the day but is turned ON during the first hour, you'd have to change
the value

Better, I suppose, to start with the status at the end of the day in row 3,
and have your time buckets start in row 4.

Now, the formula in B4 would be

=IF(NOT(ISBLANK(D4)),"Y",IF(ISBLANK(C4),B3,"N"))

so..if there is an entry in column D (the ON column), then the machine is
ON. If no entry in column D, and no entry in column C (the OFF column), then
repeat the previous period's code. If the Off column has an entry, then the
equipment if OFF.


"DebC" wrote:

Thank you for replying, and I do apologize for not explaining properly. I
will try again to clarify. There is a separate worksheet for each day.
Times in column A will be prefilled (could span 1 hour or up to 3 hrs) The
operator enters the times he shuts off and turns back on.
I will try to answer your questions below:
1-In a 24 hour period, it doesn't matter if it starts on or off; that will
carry forward from the previous day. If equipment was on at 12:59, previous
day, then a 'Y' in the first time slot, 1a-2:59.
2-I need the formula to give a 'Y' if it is on at any time during the
specified times:
so when it is turned off at 5:00, the 5a-6:59 slot is a 'Y'. and when
turned back on at 9:15, the 9a-10:59a is also a 'Y'. It ran at no time
during the 7a-8:59a time slot, so this is 'N'.
3-If turned on and off within the same time period: again, I need a 'Y' if
it runs at any time during the time slot (this has been one of my biggest
problems)
4-In the actual worksheet I would prefer to use actual time format, but can
work with either. I can also work with it entered as a decimal.
Again, thank you so much for any help you can provide. I hope I have
addressed everything, but my experience is limited, so bear with me.

"Duke Carey" wrote:

Perhaps the lack of a response is because your question leaves out some
critical info.

In the example you show, how do you determine it is ON at the beginning?
In your example, it is ON even in the time slot where the OFF is registered.
Is that the way you want it done?
What happens if the equipment is turned OFF and ON within the same time
bucket?
How are your 'times' entered? Are they really times or are they text
entries that look like time.

"DebC" wrote:

I am sorry to repost this question, but I have not received a response, and I
cannot figure this one out. This forum has helped me previously and I know
if there is an answer, someone here will have it! Thanks for your patience
(and please excuse my impatience).

This worksheet tracks equipment run time. Is there a formula that could
automatically enter a 'Y' or 'N' in column (B) based upon time it is turned
off and on in columns (C) and (D)? You will notice that even tho it was
turned off at 5am, there is a 'Y' in that slot. The actual worksheet tracks
24 hrs/day.
Thanks for any help.

(A) (B) (C) (D)
-----------------------------------------------
ON? OFF ON
*Y / N (time) (time)
---------------------------------
1a-2:59a Y
3a-4:59a Y
5a-6:59a Y 5:00
7a-8:59a N
9a-10:59a Y 9:15
11a-12:59p Y