We had a post on the forum recently asking how to create a timer in Excel, to record how long things took to happen on a model railroad. You can use the same code to time anything of course, like how long your code is taking to execute.
A follow up request asked for code to count down to a date and/or time. So in this post I'm going to provide code that does both.
Update
Following a few requests, I modified the code so that the elapsed time does not reset to 0 when the timer is stopped then restarted.
This new code allows time to accumulate over multiple Start/Stop clicks. Download the code below
Enter your email address below to download the Excel workbook with the timer code.
Download Excel Workbooks
Time Resets to 0 When Stopped and Restarted Timer-Stopwatch.xlsm
Time Accumulates When Stopped and Restarted Timer-Stopwatch1.xlsm
Timing Algorithm
For the countdown I'm going to use the Timer function which gives us the number of seconds elapsed since midnight.
Knowing that we're basing our time calculation on the number of seconds since midnight, we don't want to go past midnight whilst we're timing something or our calculations will be wrong.
If you want to start timing something before midnight, and finish timing it after midnight, you could use NOW to work out time elapsed.
The algorithm for our code is:
Save StartTime
While Stop button hasn't been pressed:
Check elapsed time
Display elapsed time on sheet (CurrentTime - StartTime)
Display elapsed time on status bar
When Stop button is pressed:
Exit code
We can implement this as two subs. The first does the timing and display to the screen, the second sets a flag to indicate the Stop button has been pressed and the code should end.
These subs will be assigned to shapes so the shapes act as start and stop buttons.
The Stop button just sets a value in a cell. The timing sub monitors this cell until it sees the value that indicates it's time to stop.
Here's what the code looks like
You'll notice I've actually written a third sub called ResetTimer which just resets cell A1 to 0:00:00. This isn't really needed so you can remove it if you want.
Here's what it looks like in action
I've included code that displays the elapsed time in the status bar as the timer is running
Application.StatusBar = ElapsedTime
When the timer is stopped the elapsed time is removed from the status bar
Application.StatusBar = False
If you don't want to see the time on the status bar just remove these lines.
DoEvents
One thing we must do with code like this is use DoEvents.
When we call DoEvents it allows Excel to do other things, like check if the Stop button has been pressed.
If we just kept looping around displaying the elapsed time, Excel would hang.
Displaying the Elapsed Time
I'm using the format function to display the time as hh:mm:ss.
There are many formats you can use to display numbers, dates, times, serial numbers and strings, so read up on what the function can do.
NOTE: We have an in-depth guide if you want to learn about custom number formats.
Countdown
For the countdown code we need to (obviously) know the date/time we are counting down to. So I'll read that in from a cell on the sheet and call it ZeroHour.
To work out the numbers of days, hours, minutes and seconds until then, just call the NOW() function and subtract one from the other:
ZeroHour = Range("A5").Value
TimeDifference = ZeroHour - Now
By doing this we'll end up with a time serial number stored in TimeDifference. If you aren't sure what a time serial number is, read up on calculating time in Excel.
To display the time left we write it to a cell on the sheet, but we have to format it correctly first. As the value in TimeDifference is a time/date serial number it will look like 123.456.
If I just try to write that out to a cell, Excel will try to display the integer part (123) as the number of days since Jan 1st 1900. Of course we want to show the number of days from now until our target date.
To do this I use the INT() function to get rid of the decimal part of the serial number, which is the time, leaving me with just a number of days. I can then stitch all of this together like so:
Range("A6").Value = Int(TimeDifference) & "d " & Format((TimeDifference), "hh:mm:ss")
Times and Dates in the Past
If you try to use a time or date in the past, the VBA will catch this, display an error message and then exit.
Here's what it looks like in action:
Download the Workbook
Click here to go back to the top of the page and get a copy of the code.
Volatile User Defined Functions
Matt
Great Timer.
Quick Question, hopefully this sub is not dead, Is there a way for the timer to continuously countdown without having to click start or Stop?
Kind regards
Philip Treacy
Thanks Matt. You need some event to start/stop the timer. You could write a macro that starts the timer when the workbook opens and then you could stop it after a set period of time or maybe at a set time.
If you have a specific problem you could post it on our forum and we can help out there.
Regards
Phil
Romain Lopez
Hi. That’s so great. I would like to be able to pause the stopwatch and restart with the current time elapsed. For example, I hit start. Then, after 1 minute, I want to pause so I hit pause. Then, a few minutes later, I hit resume and the time will resume from 1 minute and so on.
Could you please tell me the lines I need to add please?
Here is the code:
Sub StartTimer()
Dim Start As Single, RunTime As Single
Dim ElapsedTime As String
Sheets(“New”).Range(“C1”).Value = 0
Sheets(“New”).Range(“B1”).Interior.Color = 5296274 ‘Green
Start = Timer ‘ set start time.
Debug.Print Start
Do While Sheets(“New”).Range(“C1”).Value = 0
DoEvents
RunTime = Timer
ElapsedTime = Format((RunTime – Start) / 86400, “hh:mm:ss”)
Sheets(“New”).Range(“B1”).Value = ElapsedTime
Application.StatusBar = ElapsedTime
Loop
Sheets(“New”).Range(“B1”).Value = ElapsedTime
Sheets(“New”).Range(“B1”).Interior.Color = 192 ‘Dark red
Application.StatusBar = False
End Sub
Philip Treacy
Hi Romain,
I had to make changes in a few different places in order to get this working – please download this file to see the new working code.
Regards
Phil
Richard
fab code. just what i needed. simple and works almost perfectly…
unfortunately the timer blocks all further work on the data sheet while it’s running.
any ideas how to fix that?
Richard
sorry team, i just saw that i am not the only one with the problem. yes, i had already chosen the option to put start time in one cell and end time in another and to calculate the difference. i need to have a running timer on screen tho. i can’t see from the other comments whether anyone found a solution.
Catalin Bombea
When a cell is in edit mode, no code can run unfortunately.
You can try a modal form, opened in a different excel instance, will allow you to have the userform timer while you edit the other instance workbook sheet.
The only way to have code running and sheet editing is if the excel instance are not the same. It’s a complex scenario though.
Ruben
I am a rookie in VBA. But I was wondering. Would it be possible to add a -20 seconds button and a + 20 seconds button?
Catalin Bombea
Hi Ruben,
Not sure how you want it to work, but keep in mind that the code will just compare the start time with the time when you press the stop button.
You can change the calculation for ElapsedTime: (RunTime-Start+TimeSerial(0,0,20)) to add 20 seconds to the difference.
Simone
Thank you Catalin, your suggestion is a good idea but I would prefer to see the time running while I’m completing the table and not only to see the start time and the final one. Do you think it could be possible? Thank you
Catalin Bombea
Hi Simone,
See this discussion, there is no real workaround. While you edit a cell, no code will run.
Default windows timer is not an option?
If you search your computer for Alarms & Clock, there is a windows app with this name. It includes a stopwatch and a countdown timer.
It might work though, if you design a crazy flow like: use the code to display timer in a modeless userform instead of cells, then in your current file you are working on write code to open the timer file in a separate new instance of excel, that will be completely independent. In 2 separate instances of excel, 2 different codes can run in the same time, it’s not possible in the same instance.
Simone
Thank you Catalin for your suggestions
Regards
Simone
Simone
Good morning, the timer stops when I edit a number or a character in a cell. I believed that the command DoEvents would avoid this but I was wrong, can you please help me?
Thank you
Simone
Catalin Bombea
Hi Simone,
Can you explain what are you trying to achieve?
When a cell is in edit mode, no code can run, therefore OnTime will fail.
Maybe there’s another way, if you can clarify the needs.
Simone
Hi Catalin, thank you for your reply. I should start the timer and in the meantime edit in the cells but unfortunately I found that as soon as I edit in a cell the timer stops. Is there a way to solve this?
Thank you
Simone
Catalin Bombea
Why do you need the timer? What things needs to be measured?
Simone
I need to take the time I spent to complete a table in Excel. So the target is to start the timer, to complete the table by adding the proper numbers in the cells and finally to stop the time when I completed the table. Is it possible?
Thank you
Catalin Bombea
My guess is that you don’t need a timer for that.
The start button should just fill in a cell the start time, same for stop button, should put in the next cell the time when you press the button.
You will end up with the 2 dates-times you need.
Randy Perry
I’m trying to duplicate a count up & down timer based on a reference time to count up/down to..
For example my reference time is 10 minutes. One timer will count the elapsed time from 10 min. the other timer will tell me time remaining.
For example: reference time is 10 minutes, and 3 minutes have passed. Elapsed time will show 3 minutes. Remaining timer will show 7 minutes. Both values are positive.
I have downloaded several countdown timers, however, I have not solved the time remaining timer.
Philip Treacy
Hi Randy,
You only need to set up 1 VBA timer in this scenario. If this timer is counting the elapsed time then in another cell you have (10 minutes – elapsed time) to give you time remaining.
Regards
Phil
Burak
Is there a way to insert vertical alignment lines for easy following between If & End If or Sub & End Sub etc. as in your code?
Philip Treacy
Hi Burak,
Not in the VBA editor. Those images are taken from inside VS Code. I put the VBA in there just to take those images. I wish Microsoft would improve the VBA editor.
Phil
Susanna
Hello,
This code is useful . But I want to take in user input while the timer runs. The timer stops anytime I try to type something in the worksheet
Can you please help me with that
Philip Treacy
Sorry Susanna, if you try to enter something to the sheet Excel will stop the VBA from running.
You could write your code to take user input from a msgbox or a userform while the timer us running though.
Regards
Phil
Adrian
In the example code I see:
*****
Dim counter As Long
…
counter = 0
*****
But then I don’t see counter referenced elsewhere in the code. Just extra lines or was this intended to be part of the algorithm?
Philip Treacy
Hi Adrian,
That piece of code is in the image which I must have taken before removing counter. If you download the workbook you’ll see that counter isn’t in there. I’ll have to update the image.
Thanks
Phil
Matt
Hi,
Is it possible that when this excel be “shared” and opened, for example I will use this file for a game by which all users have access to the excel file with the timer. so if I click start, all the user will simultaneously see the countdown on their own PC. and when I do an action like stop, it will also make the countdown stop for the other users?
Catalin Bombea
Short answer: No.
It may be possible to save a text file in a folder that may be sync’d to a cloud storage where everyone has access to, like OneDrive, Google Drive, Dropbox, but there are some times needed for sync agents to detect a change in a file, upload it to cloud, then other computers sync agents need time to detect a change in cloud and download it to user computer. Will never show instantly on other computers, there will be a delay from the moment there is a time change until that change reaches other computers. But when you stop the clock, that final value will be what they see, even if not in real time (after a few seconds).
Claude Albertario
Hi,
Thanks for the help.
In your example, is it possible to have a pre-defined number of minutes for the timer to countdown rather than having to check a cell?
ZeroHour = “Xminutes”.Value
TimeDifference = ZeroHour – Now
Catalin Bombea
Hi Claude,
You can try to set Start=Now():
Start=Now()
Do Until Now() >= Start+TimeSerial(0,5,0)
The loop will end when the current time is equal or higher than start time + 5 minutes.
Josiah
Excellent code that works well, but I do have a question. Say I wanted to start a timer at the start of a multi-day event. Would it be possible to start a timer on a document on my computer, save the document, exit the document, and email or send it to others or put it in a share drive, and have it constantly keep running and display the same accurate running timer across all other copies on other computers for other users? Any help would be greatly appreciated.
Catalin Bombea
Hi Josiah,
I’m afraid that there is no easy answer for that, there is no connection between computers if they are in different locations, but there is always a workaround. If you put the file in a shared cloud storage folder, the code can be modified to store the times in text files for each computer running the file, and the code can also collect all text files created by all computers (each file will have computer name in file name, so each file should have different file names).
There will be a delay between the moment when the time is stored in the text file and the moment when cloud sync agent detects a change and downloads the updated text files, this delay depends on sync agent, internet speed, computer speed, and so on.
iya
Hi,
I am new to vba and have downloaded the your file. I am trying the above code to show the elapsed time. Upon adding the vba code at the end of the existing code, I am getting an error. I have added sheet2 to on the workbook.
Your response is greatly appreciated.
Regards,
Iya
Philip Treacy
Hi Iya,
I’ll need to see your code to figure out what is going wrong. Please open a topic on the forum and post your workbook there.
Regards
Phil
Michael
I really like this code. However, it lacks 2 elements of a real ‘stopwatch’.
1) When you Pause the clock, then re-Start the clock, the time has not paused, but instead has kept running. So, by starting it back, we have not really Stopped the clock, only Paused to see the time when Stop was pressed.
2) When you press Reset, then Start, it doesn’t start over at 0:00. Instead it takes up where it left off when you pressed Pause.
Also, there needs to be a way to count down as well as count up.
I’ve been trying to ‘fix’ these issues but find my VBA skills lacking. Will you help me make this a really great stopwatch?! Thanks.
Catalin Bombea
Hi Michael,
Please use our forum to upload your sample file with code, It will be much easier to help you.
Just create a new topic after sign-in.
Matheus Castro
Thanks for the code, cheers. I am trying to add sound to it, when the timer has reached the elapsed time. Any tip or trick how to do it? I need to alert people whenever it happens
Catalin Bombea
Hi,
Have you tried this code?
Beep
You can add it multiple times, like Beep: Beep: Beep
Danielle Buckingham
Thank you for sharing! If you wouldn’t mind providing feedback on the following application of your template- see below. The basics: when green the timer stops and resets, when red the timer begins to count/ collect time- displaying the running value in the cell. Currently the program only counts displays up to one second before terminating.
Range(“A937”).Select
If ActiveCell.Interior.Color = 16777215 Then
ActiveCell.Interior.Color = 5287936
ElseIf ActiveCell.Interior.Color = 5287936 Then
ActiveCell.Interior.Color = 255
ElseIf ActiveCell.Interior.Color = 255 Then
ActiveCell.Interior.Color = 5287936
End If
Dim Start As Single, RunTime As Single
Dim ElapsedTime As String
Dim Counter As Long
Range(“A937”).Value = 0
If Range(“A937”).Interior.Color = 255 Then
Counter = 0
Start = Timer
Debug.Print Start
Do While Range(“A937”).Value = 0
DoEvents
RunTime = Timer
ElapsedTime = Format((RunTime – Start) / 86400, “hh:mm:ss”)
Range(“A937”).Value = ElapsedTime
Application.StatusBar = ElapsedTime
Loop
Range(“A937”).Value = ElapsedTime
ElseIf Range(“A937”).Interior.Color = 5287936 Then
Range(“A937”).Value = 1
ElseIf Range(“A937”).Value > 0 Then
Range(“A937”) = Format(0, “hh:mm:ss”)
End If
Application.StatusBar = False
Philip Treacy
Hi Danielle,
When I run this code it does this the first time:
1. Changes the ActiveCell to green
2. Changes the value of the ActiveCell to 0
3. Checks if the AC is red (it isn’t)
4. Checks if the AC is green – it is so it sets the AC value to 1
5. End of Sub
The next time I run it it does this
1. Checks if the AC is green – it is, so it changes it to red
2. Sets the AC to 0
3. Checks if the AC is red – it is so it enters the Do While loop
4. Sets the AC to ElapsedTime and then exist the Do While because the AC is no longer 0
The 3rd time I run it it does this
1. Checks if the cell is red – it is, so it changes it to green
2. Sets the AC to 0
3. Checks if the AC is red – it isn’t
4. Checks if the AC is green – it is so it sets the value of the AC to 1
So it is doing what it is coded to do. I’m not sure what you actually want it to do?
If you use F8 to step through the code, which is what i did, you can see what it is doing.
You can read this topic about debugging VBA
If you need any more help with this, please post a topic on the forum and supply your entire workbook.
Regards
Phil
Pratik Kumar
Hello, Thank you for this beautiful elapsed time code. I am a beginner in vba macros and just learning by errors. I have used your elapsed time loop in the beginning of my script and the problem is that the code is stuck in the do While time loop and not executing the next lines of codes. Is there any way to run this loop while other codes are getting executed at the same time?
Catalin Bombea
Hi Pratik,
Only one code can run at a time. Multiple codes can run in the same time only if each code is running from its own excel instance.
The Timer code can store data to registry, all other codes should contain code to look into registry to display that timer value.
If you want to go this way, you should open a new topic on our forum, you can upload there sample files and codes.
Marcus Barrozo
While the time is running I need to insert some values in the same worksheet. If I do this, time will run out. Is there any way to make this possible?
Philip Treacy
Hi Marcus,
When you type something into a cell and enter Edit mode, Excel stops running the macro. That’s just the way it works.
Depending on what it is you are entering though, could you build a macro to take user input and then write that to the sheet? This way the macro keeps running and the timer keeps timing.
Regards
Phil
Joe Bishop
Love the stopwatch. Is it possible to add some code so it would create a historical copy of time elapsed for each start and stop event
Catalin Bombea
Hi Joe,
Anything can be done. Instead of displaying the result, just save it in a new row in a separate sheet. Add this code at the end of your existing code:
Dim Wks As Worksheet, NextRow As Long
Set Wks = ThisWorkbook.Worksheets("Sheet2")
NextRow = Wks.Cells.Find(What:="*", After:=Wks.Cells(1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
ThisWorkbook.Worksheets("Sheet2").Cells(NextRow, "A") = Start
ThisWorkbook.Worksheets("Sheet2").Cells(NextRow, "B") = ElapsedTime
iya
hi, i adding it at the end of the code but it gives an error as invalid procedure for the set wks line
Philip Treacy
Do you have a sheet called Sheet2?
Please open a topic on the forum and post your code in there so we can see what you have.
Regards
Phil
Bob
I have a sample file and would like to run multiple timers at the same time. How would I be able to tweak this code to meet my needs?
Philip Treacy
Hi Bob,
Unfortunately with VBA you can’t have multiple bits of code running at the same time, it is single threaded.
Regards
Phil
Mike Ponzio
****WARNING *** NEWBIE ***
Can anyone recommend how I can go about adding the functionality of starting and stopping the clock automatically when I open or close the workbook it is contained in.
Catalin Bombea
Hi Mike,
All you have to do is to use the Workbook_Open & BeforeClose events from ThisWorkbook module, just add StartTimer in Open event and StopTimer in the BeforeClose event:
Private Sub Workbook_Open()
StartTimer
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopTimer
End Sub
NJ van Heerden
Hello
I downloaded this workbook and the Timer runs fine on the relevant sheet. Great job!
I now added as a module. If I call StartTime via the Workbook.Open event it grey’s out my Home Menu tabs until i stop the timer.
Any idea why this happens?
Mynda Treacy
Hi,
Are you using DoEvents ?
It’s hard to say what’s going on without seeing your code. You could open a topic on our forum and attach your workbook so I can take a look.
Ry
Never really used macros before so how can I copy the stopwatch to have several of them on one sheet?
Thanks
Catalin Bombea
Hi Ry,
You need to copy the StartTimer and ResetTimer Procedures as many times you need, rename each copy StartTimer1 and ResetTimer1, StartTimer2 and ResetTimer2, and so on. Change the cell references in these new procedures as needed, from Range(“A1”) and Range(“C1”) to the cells you want. You will also need to copy the buttons from sheet, and assign to them the new macros: right click the shape-Assign Macro-choose the procedure from list.
Of course, you cannot have more than 1 timer procedure running at a time.
Catalin
Mark
Hi Catalin
I already do the procedure you tell for me to have multiple timer on single sheet .. but the problem is the 1st timer stop when the second timer is running ..its like they are displaying one single timer
Catalin Bombea
Hi Mark,
Already mentioned that only one code can run.
We’ll continue on forum, if you can provide a sample file and details.
Catalin
julian
Hi Phil,
I tried to change Format(elapsed time,”hh:mm:ss”) to Format(elapsed time,”mm:ss”) and Format(elapsed time,”ss”) in case the elapsed time is less than 1 hour or even 1 min only in order to keep a concise time format. Unfortunately the calculation was just cycling the second portion and displayed the number of min as a fixed number, say “12”. Could you please tell me what’s wrong? Thanks very much.
Best
Julian
Catalin Bombea
Hi Julian,
Hard to say without seeing your file. Can you upload on our forum a sample file with your code, so we can see your data type? Create a new topic after sign-in, we’ll see what’s wrong.
Peter
Is it possible to show up unlimited minutes like in XNote Stopwatch? I mean 123:45 for example.
Catalin Bombea
Yes,
The cell should have this custom format: [h]:mm
Cheers,
Catalin
Kelly
This is wonderful! I think I will be able to use this at work for setting standards.
Philip Treacy
Glad you find it useful.
deb
i’d like to modify this to display the number of days remaining to elect to purchase something. is it possible with this?
Catalin Bombea
Hi Deb,
Yes, anything is possible.
TimeStart should be stored in a cell in this case, because I doubt that the macro will run 30 days or less. It should look like:
ThisWorkbook.Worksheets(“Sheet1”).cells(1)=Date
Then, the TimeStop should be :
TimeStop=Date
Next, the final evaluation should be:
Msgbox 30-(TimeStop-CDate(ThisWorkbook.Worksheets(“Sheet1″).cells(1))) & ” Days left!”
Julian
I’ve seen another countdown timer displayed the remaining days / hours / minutes / seconds. Could you please also provide a sample file to demonstrate it? It would be perfect.
Philip Treacy
Hi Julian,
Sorry this reply has taken a while, I had to write new code for this.
I’ve updated the post and the workbook, so download the workbook and you’ll find the new code for the countdown in there.
Regards
Phil
Julian
Hi Phil,
I do appreciate your effort to work out the new code for me. However, it seemed something wrong with the download link. Could you please fix it at your convenience. Thanks again.
Best
Julian
Philip Treacy
Sorry Julian, try to download it again.
Phil
julian
Hi Phil,
Is it also a VBA workbook? I found it doesn’t link to any file below.
https://www.myonlinetraininghub.com/timer-stopwatch-excel-vba#dl_anchor
Best
Julian
Philip Treacy
Hi Julian,
That link scrolls the page back up to the top where the download link is.
Phil
julian
Hi Phil,
Yes, I found you’ve added the additional feature in the original file. Thank you very much.
Best
Julian
Philip Treacy
No worries.
Phil
Abbott Katz
Great timer macro – thanks!
Mynda Treacy
you’re welcome 🙂