#1
|
|||
|
|||
Trying to set countdown timers in Excel. Is it possible?
Hello all, hoping somebody knows how to make this work, or can state it is not possible.
I have 16 industrial ovens. At the end of the cook cycle, I must chill the product, and must chill it in a certain amount of time. I want to alert the operators to check temperatures at certain intervals. An example of that is below. At the vary top row, I’d like to display the time. As in =NOW() The next row, (and 15 more below it, 1 row for each oven) column A would have the oven identifier. Column B would have the time at the end of the cycle, manually input. Column C, D, and E would indicate the first check, second check, and third check times. I thought this would be as simple as manually inputting the end time of cook in B, and then simply do a “+5” (meaning add 5 hours) in column C reference column B, and a +5 in column D referencing C, and then something like “+10) in column E, referencing D. Then do a conditional format in each for C, D, and E, referencing time now from the top. Make the cell turn yellow when it gets to be 30 minutes or less from time needed, and then Red when 5 minutes before the needed time. But apparently, not so simple. Help? THANK YOU! |
#2
|
|||
|
|||
Here is part of the project you are seeking. What remains is for you to create the
CONDITIONAL FORMATTING for the cells to change color as you outlined. Now ... you could get someone to create that as well but you would not learn anything from someone else doing all the work for you. GOOGLE "excel conditional formatting" to begin learning. Best wishes. Code:
Option Explicit Public RunWhen As Double Public Const cRunIntervalSeconds = 1 ' 1 seconds Public Const cRunWhat = "The_Sub" ' the name of the procedure to run Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=True End Sub Sub The_Sub() Application.Cursor = xlNorthwestArrow 'prevents mouse cusor blinking DoEvents With ActiveSheet .Cells(1, 2).Value = Format$(Now, "hh:mm:ss") End With DoEvents ' Call StartTimer to schedule the procedure again StartTimer End Sub Download link : https://www.amazon.com/clouddrive/sh...nmh85DwnN6D5qj |
#3
|
|||
|
|||
Logit
I can't thank you enough for the help! Amazing! This will help our oven crew greatly, and hopefully keep them out of trouble. Again, Thank You and hope the best for you! |
#4
|
||||
|
||||
In the attached Sheet1
Formula =NOW() in cell A1 Formulae don't all update themselves on a sheet, even if Calculation is set to Automatic, so there's a bit of code to make it re-calculate every 10 seconds. I've included a facility to double click the cells containing manually updateable end-of-cycle times (Column B) for each oven whereby if you double-click one of those cells in column B it will put the current date and time in that cell, but only if that cell is empty to start with (this is to stop accidental update of a time if you miss the cell you want to double-click on). So once you've put a time in column B there is conditional formatting in columns C, D & E as follows: Yellow within 30 mins Red within 5 mins Cross-hatch yellow overdue I've also added something to allow you to demonstrate/test in real time over about half an hour: For Oven 10 to Oven 15 there are formula (cells B11:B16). If you double-click cell B17 (after deleting its current contents) the current time will appear in it, and the cells above will show some simulated end-of-cycle times. If you leave the sheet running, after about a minute you should see some cells changing colour, leave it running for another 25 minutes and you should see some more colour changes, and after half an hour, some more. After you've satisfied yourself that it's running correctly you can of course delete all the entries/formulae in column B and start using the sheet in earnest. Be aware that the date/time stamps should include a date since your check times extend to nearly a day (20 hours) so are almost guaranteed at times to run over midnight from one day to the next. The double-click facility will allow you to put in an almost correct date/time stamp which you can then adjust manually. You will be able to save and close the sheet while it's running, open it later and all the entries and conditional formatting should still be correct (as long as your computer's clock is correct). I've protected the sheet (no password) leaving only the light green shaded cells editable. I've left most of the cells formatted including seconds (and not showing the date) for testing/demonstration purposes, but it might look nicer if the fomatting only showed hours and minutes. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Adding multiple clocks and timers in slides | sid | PowerPoint | 0 | 08-20-2015 04:40 AM |
Help Needed on countdown timers | StWeiler | PowerPoint | 1 | 12-20-2012 02:56 PM |
Countdown Timer | mcdanita | PowerPoint | 0 | 03-08-2012 12:51 PM |
Count Up Timers in PowerPoint | chome4 | PowerPoint | 0 | 02-05-2012 11:04 AM |
Animated countdown | Ghost World | PowerPoint | 4 | 09-21-2011 12:46 PM |