How to Create a Countdown Timer Clock in Excel and VBA

This is a simple tutorial of how to create a simple countdown in Excel 2007 using Application.Ontime event in VBA. this is a backbone and template you can use to create a lot of different functions based on time. if you have requests or would like to see how to do some other creative solution in excel, please mention in the comments and I will try to see what I can do to help or show how.
I have another video showing how to update PowerPoint content using excel and VBA here • How To Update PowerPoi...

[@dagultara3739]

Yes. A video posted more than 2 years ago and it helped an Excel newcomer. Thank you. Now all I need is to learn how to place a textbox for each of my sheet and triggered only by one button.

[@jwf3148]

Your video tutorial format and presentation is EXCELlent !!! Much appreciation for this clear and concise presentation.

[@MalaysiaTamilanVlogs]

i am 12 old year boy but i done it with this video☺☺☺.best video ever

[@ckrampertracing]

Thanks for posting! I adapted this to count upward and added a reset button.

[@quechismoso]

This video was so helpfull, but do you know how to make it start again it self?

[@darrylstipsadventures674]

good video....got eveything answered after reading entire post thx alot

[@deannac8639]

Great tutorial...Something I really needed for a project that I am working on. Thank you!

[@ArnellLoïck]

Is it possible to apply a timer to the Emails ? Say every 7 months for exemple it sends an Email. What would be the formula ?

[@adilauliarn2852]

thank u sir my homework is finished

[@ricro880]

Thanks for this explanation. How do I get this timer to be active and visible in several worksheets in the same workbook

[@darrylstipsadventures674]

How would you go about adding a reset button that would reset back to original start time? thx

[@juliengure6773]

Here is the code for 2010 excel user .thanks a lot to anameiwontforget Sub starttimer() Application.OnTime Now + TimeValue("00:00:01"), "nexttick" End Sub Sub nexttick() If Worksheets(1).Range("B1") = 0 Then Exit Sub Worksheets(1).Range("B1").Value = Range("b1").Value - TimeValue("00:00:01") If Range("b1").Value

[@chandarpoorna9656]

thanks

[@ArnellLoïck]

Is it possible to apply a timer to the Emails ? Say every 7 months for exemple it sends an Email. What would be the formula ?

[@r4m1ng03]

Good. Exactly what I was looking for.. the problem is that o Mac if I start the timer and then I select another application to do something else, the timer gets frozen. It will continue only if I select excel window again.. any idea?

[@bradbeeksma3239]

Excellent demo! Works perfect thanks so much

[@YatraShubh-MangalmayeHo]

Sir I want to make a timmer eg if I have 24 hours left to complete one work and I would go to the sheet to look at the time each task how many hours are left . Can I make that one will run anti clock wise

[@howieleem]

Interesting video. I have the timer working now as you described it. What I need is a timer that counts down from 30 days to zero. Is this an easy mod to the code you wrote?

[@cassandranicolepallanan1643]

Hi may i ask how to add sound effect when 10 seconds before end of timer

[@HarshSingh-ie8ou]

Hi i have a big concern here. If i click on 'Start Timer' multiple times, the clock runs faster. Like if i click thrice, it runs on 3x speed. Can you please help me eliminate this issue?

[@rtleo]

start with stop button disabled, then disable the start button on press and enable stop button, and vice versa

[@chriscammock3289]

the video is really good at showing how to make a countdown timer. Do you know how you would make a thrid button, a reset button and code, so it starts resets the counter to for example 5minutes and you just have to press start counter. To start it all again

[@iivioviv7350]

Thanks for the help. I'm moving forward . Could you make a video of something super cool I can do. Please. I'm in the process of re-learning. Again. Huge thanks for the help.

[@nahidmukadam]

first of all i would like to thank you for your information to help me to completing my project. but i do find some problem as like 1)if timer is on and if i do want to type in another cell the timer is been stop. 2)and when timer is complete it delete the formula from the reference cell as like cell "C:6" IS my number value (for example:- 90) and cell "P:3" has formula of =DOLLARED(C6,60)/1440 With format of time"00:00:00" so that if i type 90 in cell(C:6) it convert with the formula and show value (01:30:00) in "P:3" and ''P:3" is the reference to my text box

[@cathall1380]

THANK YOU FOR THIS AWESOME VIDEO! SUPER helpful!!!!

[@maggot95mustaine]

So simply and easy tanks

[@eluszopirar]

Hi sir. Can you combine this method with randbetween?

[@AaronGong1]

It seems to be working very well, but every time it ticks, my cursor flashes. The timer is part of a much more complex sheet. But no other subs are running and it still flashes. The workbook still works correctly but it's annoying. Any advice?

[@melissawhyman9690]

This is fantastic. Thank you!Is there a way to set the start timer to then open a text box for writing and when the timer is done to close it? I am a teacher trying to figure out a way to set up a 3 minute timed writing. This is the closest I have gotten to what I want. Thanks!

[@JRotch64]

Thanks for the help! One question. How can I get a count-up timer to go past 23:59:59 without resetting to 0:00:0? I tried changing the cell format but no luck.

[@kowloonsportsclub]

do you know why i can not set the countdown timer clock in the excel 2010 Mac ? the Excel just appear error 440 , what is that mean ? anything i can do for it ? thank you for your teaching.

[@HozM10]

Hello, Like your video, I have no programing skills and am trying to do something like what you did but I need to do something like this with out adding the text box. I need that when I enter any information to cell B5 cell or text box or button starts counting down. Then when I enter info on another cell say cell c57 it stops or if I don't enter anything in any other cell I can click on the count down cell/text box/button to stop it. Can you help with that. I also have an existing VBA on this work sheet so I just want to add it in to that VBA instead of creating a new module if that is possible.

[@terrymadeley]

Great video, many thanks. Very clear.

[@piotrszymanski7071]

Hi will it work for multiple rows in excel? I creating spreadsheet with multiple tasks on it and wants countdown time for each one. BTW thanks for video.

[@jkoppel1]

I created a timer exactly as in the video with the exception of the time to begin the countdown. Sheet1.Range("B1").Value is a string variant and is an integer variant, causing a type mismatch error. 1. Why does it work in the video but not on my desktop and 2. how do I resolve this?

[@kevinvaughn7341]

Thanks for sharing this with us all

[@XtylishAdvertisement]

how to set excel sheet expire after count down? or after count down ask to upgrade or etc..

[@TheCveau]

is it possible to add a beep at the end of the countdown?

[@dantecpa]

you are the best, thank you.

[@anotherone4657]

Hi Can it it me user wise data? Like if I am using it and used 2 min so it can track 2 min and the other user can see who has how much time.

[@marilizevanderwalt9308]

Every time it changes to the next second after I have formatted it as mm:ss it automatically reformats to mm/dd/yyyy hh:mm. Is there any way for me to add into the code to constantly format the column as mm:ss?

[@Veer_tales]

I have made an exam in excel and set this count down timer but it pauses when i access other cells for typing.. any solution

[@drphu]

I Wanted to learn this a long time! THX (Pun intended!)

[@VicPodcast]

how would this be done on google sheets?

[@RacquelBasada]

hi, i tried to follow eveything here but the code for colors especially on "else" is not working, please help. thank you!

[@luqmanhakim8366]

hi Mr.. how about google spreadsheets, any tutorial video about it. Thanks

[@jannahashraf]

Hi, Hope you are fine, I want to ask about how to put a timer - Counter on the user-form in Excel VBA that i Made an Exam with multiple choice with 50 Questions on 50 Forms (Next By next) and want the exam is close Automatically when the time is finished, So can you help in this issue

[@zhenni2820]

HI I am using Excel 2016. And, I have entered the code exactly as the one in the video. Everthing else is fine. However, I came a cross a problem when I am at the step of setting the timer to show a red reminder when less than 10s left. The code I entered is as following: Sub starttimer() Application.OnTime Now + TimeValue("00:00:01"), "nextick" End Sub Sub nextick() If Sheet1.Range("C1") = 0 Then Exit Sub Sheet1.Range("C1").Value = Sheet1.Range("C1").Value - TimeValue("00:00:01") If Sheet1.Range("C1").Value

[@DavisBoysWyo]

@Anameiwontforget Is it possible to loop this? To automatically reset and begin the countdown again unless the "stop Timer" button is pressed? Thanks in advance!

[@Berghiker]

Great but how do you get the "Design Mode" in Excel?

[@meredithebrown1676]

I really enjoyed the video, except for one problem, no matter what I do, I cannot get my timer to start. I have double checked everything, and I am pretty stuck. I'm not exactly a novice at excel, but when it comes to programming within it I'm lost. Any suggestions?

[@meredithebrown1676]

Oh I forgot, I am using excel 2016 BTW

[@aforno1]

Thanks for the video. How can I make it run in Miliseconds?

[@fun4dje]

Is it possible to make 'unlimited' count-down timers in same sheet? like if one create a Start and stop for each row??

[@canaanjairo301]

not sure if you guys cares but if you guys are stoned like me during the covid times then you can watch all of the latest movies and series on InstaFlixxer. Been binge watching with my girlfriend recently :)

[@simongage6752]

@Canaan Jairo yup, have been using instaflixxer for years myself :D

[@abdullahboston6963]

@Canaan Jairo Yea, been using instaflixxer for since december myself =)

[@nicoliam492]

@Canaan Jairo Yea, I have been watching on instaflixxer for months myself :)

[@ninjasapporo4850]

The next thing I am wondering is how to pin it on top of windows system....

[@mahmoudelsheshtawy9824]

very good but when i try typing any thing in any cell or try to edit any cell value , timer Paused

[@OrangeBossa]

Why do you use ActiveX buttons instead of assigning Subs directly to Form Control buttons? And why the countdown jumps so fast every time you clicked start? Like from 4:59 to 4:58. Thanks!

[@MortenBN1988]

The countdown can jump the first second depending on the system time. This is caused because this timer works by counting one second down for each second the system time has counted one second forward. In the "starttimer" Sub you can see that the "nexttick" Sub is initiated every time the system time is at its current time (Now) + 1 second. With this method the first second in the countdown will always be shorter than an actual second as there's already a second in progress when you press the button. Hope you understand.

[@dantyr11]

Work nice. how do i make it start auto start again after 00:00

[@moso0258]

i want to make it with start button only and when it reaches 60 it became 0 again

[@Exploringnwohio]

Awesome, thanks for the tutorial!!

[@FlySushiBsAs]

hi i did it exactly but it tells me i have an error 424. What should i do?

[@markbouwman7847]

this doesn't work, it constantly says that it can't find the macro or that macros are not available. They are available, and these are made within sheet 1, so what am I doing wrong?

[@Nareshkumar-jx8og]

Hi, i am also facing the same issue , if i manipulating the spread sheet timer is not working properly, please suggest to avoid this

[@anameiwontforget]

Read previous thread that you also commented on. I pasted code in there to resolve this issue.

[@braraujo5]

Hi. Great video. I used it to create not a count down time but a sort of stopwatch that shows the elapsed time. It works well but I realize the macro stops to work when I am inserting content into cells. The whole purpose of the worksheet is to be able to insert the shirt number of the participants in a race as they reach the goal. so the timer shouldn't stop everytime I insert a number. Do you know why this happens and if it can be fixed. Thanks in advance

[@braraujo5]

Sorry, I should have just use my brain a bit before starting to ask for help. I managed. Instead of using the same system of adding a seconder each second I just made it calculate NOW - Starting point every second. The macro still doesnt update the cell when inserting new data but it will then continue with the right elapsed time next time it updates

[@lostinmelody1714]

It's very helpful! Can you help me to make it into an Android offline app

[@taylorbrent88]

How can I add a "reset" button that would reset the time to the original time?

[@anameiwontforget]

The easiest way to do something like this would be to add a button (change the caption to "reset timer" as seen at 0:53) , and in design mode, double click the button and then when the code page opens add sheet1.range("b2").value = timevalue("00:05:00") with the timevalue being equal to whatever you want the timer to reset to in hours:minutes:seconds format.

[@goldenboy1306]

It does work! Thank you! I have been trying to figure out how to do this forever! Also, Is there a way to set a key command for the reset? For example, is there a way for me to set up that every time I press the "enter" key the timer resets?

[@boygurl6708]

Taylors​. Wright to

[@markbouwman7847]

Sub resettimer() Sheet1.stoptimer Sheet1.Range("D30").Value = TimeValue("00:00:05") 'add reset time here End Sub 'copy and paste this

[@skim32]

Anyone find that if you close the spreadsheet while the countdown is still running and if you have another spreadsheet open. Then it will automatically reopen the timer spreadsheet?

[@oma23j]

does that work on powerpoint?

[@visualcapricorn4479]

Cool bro!

[@HarshSingh-ie8ou]

Hi, thanks a lot for this. I'm having a small issue. With the red color command, it just blinks for a second and then turns white. It does not stay red for the entire time.

[@anameiwontforget]

Please copy and paste that section of the code from your sheet so I can look at it.

[@HarshSingh-ie8ou]

Thank you for replying. i just tried once again and found the error. :)

[@HarshSingh-ie8ou]

Hi i have a big concern here. If i click on 'Start Timer' multiple times, the clock runs faster. Like if i click thrice, it runs on 3x speed. Can you please help me eliminate this issue?

[@azairvine]

Hey mate, Thanks so much for this video - really helped me out creating some countdown timers. Got a problem though - I'm trying to run 4 different timers concurrently, and they seem to be tripping over each other. The timers are not counting down smoothly, and almost seem as if they're conflicting with each other. I basically used your code and padded it out a little, calling each sub _1, _2, _3, _4 for the respective timers. When each timer reaches zero it increments a value in a cell then restarts. I have command buttons to start and stop the timers individually.

[@azairvine]

Sub startTimer_1() Application.OnTime Now + TimeValue("00:00:01"), "nextTick_1" End Sub Sub nextTick_1() If Range("AF4") = 0 Then Range("E3").Value = "Lvl" Range("AF4").Value = TimeValue("00:00:20") If Range("Y4").Value = 1 Then Range("AG4").Value = 2 ElseIf Range("Y4").Value = 2 Then Range("AG4").Value = 3 ElseIf Range("Y4").Value = 3 Then Range("AG4").Value = 4 ElseIf Range("Y4").Value = 4 Then Range("AG4").Value = 5 ElseIf Range("Y4").Value = 5 Then Range("AG4").Value = 6 ElseIf Range("Y4").Value = 6 Then Range("AG4").Value = 7 ElseIf Range("Y4").Value = 7 Then Range("AG4").Value = 8 ElseIf Range("Y4").Value = 8 Then Range("AG4").Value = 9 ElseIf Range("Y4").Value = 9 Then Range("AG4").Value = 10 End If startTimer_1 Exit Sub End If Range("AF4").Value = Range("AF4").Value - TimeValue("00:00:01") If Range("AF4").Value

[@OnomuorhoyaPeace]

hello Sir, can do this in MS Word?

[@acpcardoso]

Thank you so much for the amazing video, does anyone know how to make the counter go into negative numbers? I would really appreciate if someone could help me achieve that.

[@jwf3148]

Just thinking - at 0 you could start a count-up timer and add an indicator label stating that this is now "negative" count up, or something similar, like special formatting of the label value. So you are reporting the seconds after the count-down reached zero - and you can "think" of those seconds as being "negative" relative to the count-down timer's 0 value.

[@elaineharris9826]

I duplicated your code and I have the timer working (quite an accomplishment for a non-programmer type like myself)! I have one question. I'd like to move the text box to another sheet. I tried to cut-and-paste it to the other sheet and now the code can't find the text box. I tried updating the code to look at sheet5 instead of your sheet1. any ideas?

[@anameiwontforget]

You need to make sure the text box is linked to the cell with that countdown value. If you are working on sheet5, you could always just click on a cell in sheet5, type an = sign then click on cell B2 in sheet1. This would make that cell in sheet5 match the value of the timer as it counts down, then you could create a text box on sheet5, click on the text box, and in the formula bar type = then click on the new cell in sheet5 that you made match the value from sheet1.

[@sufyanomer7750]

Thank you @anameiwontforget this is exactly what i was looking for !!!!!!

[@rupeshmurbade9092]

nice video but start button change drop down list click 1 to start timer

[@skim32]

Anyone found that if you close the spreadsheet when the timer is still going. And you have another spreadsheet open. Then the timer spreadsheet will open automatically.

[@moso0258]

i want it to start with single button and when it became 0 it stops and became 45

[@internetnetnet3834]

no esta nitido el video y no se pueden apreciar los codigos y es una lastima me servia muchisimo

[@profitpark1737]

cool

[@lexumi5161]

it is not working coause of this line: Sheet1.Range("O6").Value = Sheet1.Range("o6").Value - TimeValue("00:00:01") it marks it yellow when i press on debuging what can i do? pls help fast i Need it for a School projekt

[@MortenBN1988]

Is your sheet actually called Sheet1? If not you need to correct that in the code or edit the name of the sheet. Also it's good practice to always write cell references in capital. You and the OP only have a capital cell ref. in the first part of the line. It should be O6 and not o6. :-)

[@coastalman454545]

Hey man, great video, but one question. I got the timer working, but I also noticed that if you did anythings else on the page or in the workbook the timer would stop on its own. How good is the timer if you can't work in the current project. It's a time management thing. Any suggestions.

[@anameiwontforget]

Good point. It was intended as just a stand alone timer, but there is a way to "fix" it and base it of actual clock, I will try to do an updated video and post soon.

[@povictory]

anameiwontforget Love your videos but I'm having the same issue in that the application that I want to use the timer for requires the clock to keep running while manipulating the spreadsheet. Anxious to hear about the fix!!

[@anameiwontforget]

Chris Thompson Copy and paste below in place of the code from the original video. no time to make a video, but bottom line is, it ties the timer to the actual time, so if you type something and manipulate the spreadsheet, the timer will appear to stop, but will catch up where it should be once the edit is done. ---------------------------------- Dim Starttime Dim timerlength Sub starttimer() Starttime = Now() timerlength = Sheet1.Range("b1") Application.OnTime Now + TimeValue("00:00:01"), "nexttick" End Sub Sub looptimer() Application.OnTime Now + TimeValue("00:00:01"), "nexttick" End Sub Sub nexttick() If Sheet1.Range("b1") < TimeValue("00:00:01") Then Sheet1.Range("B1") = 0 Exit Sub End If Sheet1.Range("b1").Value = timerlength - (Now() - Starttime) looptimer End Sub Sub stoptimer() On Error Resume Next Application.OnTime Now + TimeValue("00:00:01"), "nexttick", , False End Sub ----------------------------------------------

[@povictory]

anameiwontforget Brilliant...this works perfectly. Thank you so much!!

[@Nareshkumar-jx8og]

+Chris Thompson Hi i am also facing the same issue, when i am manipulating the spread sheet timer will appear stop , what to do to avoid this

[@jhonreyna5758]

does it works for Excel 2016

[@444pep]

it dosnt work either

[@carlosnicolasaltamiranoaci8781]

What is nexttick?

[@stevemendoza4666]

I would like to pay you for a custom timer please reach out thanks.

[@chang6277]

post a vid for using timer in USERFORM ?

[@de_luxe8616]

Thank you so much!! I tried this for hours and my code lookes correct but I still dont know where the error is..... For anyone who likes looking for mistakes: Public bloop As Boolean Public t As Integer Sub Timer_Laeuft() t = t - 1 Range("d6").Value = t End Sub Sub Timer_Stop() bloop = False End Sub Sub Timer_Start() bloop = True t = 20 Do While (bloop = True) And (t > 0) Application.OnTime Now + TimeValue("00:00:02"), "Timer_Laeuft" Loop End Sub

[@fabiodesignen8365]

vc poderia me envia essa planilha pvf

[@azhin00azha45]

does that work on office 2010 ?

[@hadrurusband]

It does

[@Berghiker]

If you don't have design mode in Excel 2016, you can't do this. I don't have a design mode button.

[@marcthormann9169]

Hi, Is there anyway to make the timer run and display in hundredths of a second? I'm really desperate right now because I need it for a classwork. I would be really thankful for your help @anameiwontforget Thanks;D

[@Skiris3]

does not work on office 2007 :(

[@artesfotosservicosgraficos9238]

vc poderia me manda esse cronometro por e-mail, por favo

[@biswal999]

Why don' you use the zoom tool of your program to show the exactly what r u doing, we are unable to read the program....Please don't be more faster

[@Berghiker]

I'm stuck at 4:17. I need to get another Excel program.

[@anameiwontforget]

www.addintools.com/documents/excel/where-is-design-mode.html

[@anameiwontforget]

answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other/design-mode-button-on-developers-tab-greyed-out/7aab80bc-baa1-41bf-9276-5de2043394df

[@Berghiker]

I need this for a Mac.

[@444pep]

It dosent Work in 2016

[@siyingw133]

always "424 false

[@limbinaun3570]

多年轻

[@Darkslide632]

Man, you need to learn the difference between a tutorial - where you walk people through things - and a video of you just doing it. You move through things so fast it's useless unless you already know what you're doing.

[@ManMotivation2024]

Why don' you use the zoom tool of your program to show the exactly what r u doin', its to small and impossible to see everything, the quality of this video is low.

[@laodemuhardinarsyad5563]

wk wk

[@rohitmalpani5081]

video quality is very bad

[@wallaguest1]

cool