Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time tracking formula (trying again)
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time tracking formula (trying again)
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time tracking formula (trying again)
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time tracking formula (trying again)
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time tracking formula (trying again)
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time tracking formula (trying again)
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). "Duke Carey" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time tracking formula (trying again)
yes, thank you!
"Herbert Seidenberg" wrote: In case you want to know the run time for each hour... http://www.savefile.com/files/1766937 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time tracking formula (trying again)
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" wrote: In case you want to know the run time for each hour... http://www.savefile.com/files/1766937 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time tracking formula (trying again)
Upload your file to
http://freefilehosting.net/ or http://www.savefile.com and I will add formulas and instructions. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time tracking formula (trying again)
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. "Herbert Seidenberg" wrote: Upload your file to http://freefilehosting.net/ or http://www.savefile.com and I will add formulas and instructions. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time tracking formula needed | Excel Worksheet Functions | |||
Time Tracking Help | Excel Discussion (Misc queries) | |||
Tracking Time | Excel Worksheet Functions | |||
Tracking Time | Excel Discussion (Misc queries) | |||
Tracking time | Excel Worksheet Functions |