Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-26-2021, 12:51 PM
waybomb waybomb is offline Trying to set countdown timers in Excel. Is it possible? Windows 10 Trying to set countdown timers in Excel. Is it possible? Office 2019
Novice
Trying to set countdown timers in Excel. Is it possible?
 
Join Date: Nov 2021
Posts: 2
waybomb is on a distinguished road
Default 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!
Reply With Quote
  #2  
Old 11-27-2021, 11:11 AM
Logit Logit is offline Trying to set countdown timers in Excel. Is it possible? Windows 10 Trying to set countdown timers in Excel. Is it possible? Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

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
Reply With Quote
  #3  
Old 11-27-2021, 12:19 PM
waybomb waybomb is offline Trying to set countdown timers in Excel. Is it possible? Windows 10 Trying to set countdown timers in Excel. Is it possible? Office 2019
Novice
Trying to set countdown timers in Excel. Is it possible?
 
Join Date: Nov 2021
Posts: 2
waybomb is on a distinguished road
Default

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!
Reply With Quote
  #4  
Old 11-27-2021, 04:06 PM
p45cal's Avatar
p45cal p45cal is offline Trying to set countdown timers in Excel. Is it possible? Windows 10 Trying to set countdown timers in Excel. Is it possible? Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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.
Attached Files
File Type: xlsm msofficeforums48052.xlsm (23.6 KB, 6 views)
Reply With Quote
Reply

Thread Tools
Display Modes


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
Trying to set countdown timers in Excel. Is it possible? Animated countdown Ghost World PowerPoint 4 09-21-2011 12:46 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:36 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft