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.
|