time tracking formula (trying again)

[DebC]

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

 

[Duke Carey]

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]

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]

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.

 

[Herbert Seidenberg]

In case you want to know the run time
for each hour...
http://www.savefile.com/files/1766937

 

[DebC]

I tried this and appreciate it very much, but unfortunately it doesn't work
each time. As I described previously:
3-If turned on and off within the same time period: I need a 'Y' if
it runs at any time during the time slot (this has been one of my biggest
problems)
So if it is on at 5 am, but turned off at any time during the 5-6:59 time
period, I still need a 'Y', so it can't be based upon whether or not a cell
is empty. Would there be a formula that could be based upon time, rather than
the cell being empty or not? (sorry for the trouble).

 

[DebC]

yes, thank you!

 

[DebC]

Is there a way to copy this into my worksheet? I do not understand how the
formula works, so I don't know how to adapt it when moved.

 

[Herbert Seidenberg]

Upload your file to
http://freefilehosting.net/
or
http://www.savefile.com
and I will add formulas and instructions.

 

[DebC]

http://www.savefile.com/files/1788734

sorry it has taken me so long to reply. I greatly appreciate your help!
Here is the link. In this workbook (I have set up for dates 1st thru 3rd,
but the actual workbook will contain 1st thru 31st, and for each month of
each year) I need to know how to determine or set whether it starts on or
off, and to have the next day begin where the previous day left off, whether
on or off. I also need a "Y", or "N" in column 'g'. I think that is as
simple as an 'If' formula. One other note here....this is a very small part
of my actual workbook; there are many more aspects, and I wondered, if I need
to move this, or add columns or rows is it a problem? As I said previously,
I don't understand the formula you provided, so I don't know how to adapt it.
I hope I have given you all the details you need. Please let me know if
not. Thank you again.