Building a simple timer using Excel VBA to track my Rubik’s cube solving speed [case study]

Posted on May 13th, 2014 in VBA Macros - 20 comments

Today, lets learn how to make a simple timer app using Excel. First some background…,

Rubik's CubeRecently, I learned how to solve Rubik’s cube from my nephew. As a budding cuber, I wanted to track my progress. Initially I used the stopwatch in my iPhone. But it wont let me track previous times. So I thought, “Well, I can use Excel for this”.

So I made a small timer app using Excel. Its quite minimalistic. It has a single button. I press it and it tracks the start time (date & time stamp). If I press the button again, it records the duration.

This way, I can see my progress over next few weeks and may be plot the trend.

Demo of the Excel VBA timer

Here is a short demo. This is what we will be building.

Building a simple timer using Excel VBA to track time - demo

Tutorial to make a timer in Excel

To make a timer app in Excel, first we need to understand the logic for this. If VBA apps can be defined on a scale of 1 to 10 (1 being easiest to develop and 10 being most complex), our timer app can be classified as 1.5. It is really simple. But nevertheless, it is a good idea to list down various ingredients and basic logic to follow.

So we need,

  • A table to store the time stamps & durations
  • A button (simple text box will do) to start & stop the timer

Set up the timer worksheet

In a blank worksheet, make space for a 2 column table. Type Time stamp & Duration as column headings and make a table from these (CTRL+T to insert the table)

Note: For the macro to work, you do not need a table. Any 2 column range will do. A table makes our timer app look sexy.

Also, insert a rounded rectangle and format it to look like a button (from Format Ribbon > Shape Styles, select something slick and pretty)

In a blank cell, type the word “Start”. Name this cell as timer.button.label

Now, click on the rounded rectangle button, go to formula bar and type =timer.button.label

💡 Tip: Yes, you can assign names or cell references to shapes. This way, whatever text is in the cell will be shown inside the shape.

Other names to make:

Although we can write VBA code without creating these names, our code will be readable with these names. So here we go:

  • Select the header “Timestamp” of the table and name it as time.stamp.start
  • Name the table as Durations from Table Design ribbon
  • In a blank cell, write the formula =COUNTA(Durations[Timestamp])
  • This counts how many timestamps are already inserted.
  • Now name this cell as count.of.timestamps

We are done. Lets roll in to VBA.

Writing the VBA code for timer

Open VBE (Visual Basic Editor) and insert a new module in your timer workbook. There write this code.


Sub startStopTimer()
    If Range("timer.button.label") = "Start" Then
        Range("time.stamp.start").Offset(Range("count.of.timestamps") + 1).Value = Now
        Range("timer.button.label") = "Stop"
    Else
        Range("time.stamp.start").Offset(Range("count.of.timestamps"), 1).Value = Now - Range("time.stamp.start").Offset(Range("count.of.timestamps"))
        Range("timer.button.label") = "Start"
    End If
End Sub

Assign this macro to the timer button

Right click on timer button and choose “Assign macro”. Select the startStopTimer sub from the list and click ok.

Now go ahead and test it. Assuming you have used same names as per this post, your timer should work.

How this macro works?

When you click on the timer button, you want one of the 2 things to happen.

  1. You want to start the timer
  2. You want to stop the timer

What you want to do can be checked with this logical check.

Range("timer.button.label") = "Start"

If this is true, then you want to start the timer.
Else, you want to stop the timer.

If you want to start the timer

Then, we need to go to the last row of the table + 1 and insert current time (now) in that cell.

This is done by,

Range("time.stamp.start").Offset(Range("count.of.timestamps") + 1).Value = Now

Once we do that, we need to change timer button’s text to “Stop”.

This is done by,

Range("timer.button.label") = "Stop"

If you want to stop the timer

Then, we need to go to the last row’s 2nd column of the table and print the difference between latest time (now) and starting time (last row, first column value)

This is done by,

Range("time.stamp.start").Offset(Range("count.of.timestamps"), 1).Value = Now - Range("time.stamp.start").Offset(Range("count.of.timestamps"))

Once we do that, we need to change the button text to “Start” by using this code:

Range("timer.button.label") = "Start"

That’s all. Our VBA code is rather simple.

One last step, formatting the duration

If you look at the duration, it could read something like 0.0042354. This is because duration is displayed as a fraction of day. So 0.0042354 means the duration is 0.42% of a day.

Now, wouldn’t it be better if we can show this in minutes and seconds?

To do that, select the entire table column of durations, press CTRL+1

Then, set formatting as custom and type code as [mm]:ss

And you are done!

Download Simple Timer Excel VBA workbook

Click here to download Simple Timer Excel VBA workbook. Play with it. Use it to track your Sudoku, crossword or knitting times. Or even Rubik’s cube times. See what trends and patterns you can uncover.

Do you use Excel for tracking time?

I know many companies use Excel based trackers to keep track of employee time. I personally use time tracking features of Excel for needs like this all the time.

What about you? Do you use Excel time functions like NOW, TODAY and VBA to track progress? What techniques you apply? Please share using comments.

Like tracking? You will love these

If you track things with Excel, you are going to find below tutorials very useful.

Note: Rubik’s cube image by Booyabazooka thru Wikimedia

Written by Chandoo
Tags: , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

20 Responses to “Building a simple timer using Excel VBA to track my Rubik’s cube solving speed [case study]”

  1. Chris Macro says:

    I always enjoy seeing Excel used for situations outside of the workplace! A nice little addition to this (and I think teachers might especially find this useful while administrating timed tests or presentations) would be to make the counter visible on the spreadsheet. Another cool tweak would be to change the fill color red once you have gone over a time that has already been recorded in the table. As you probably can tell, there are a lot of variations you could make using this example! Great job!!

    • Tyler Barr says:

      Chris -- I just sent a file to your e-mail I found on your blog site. Let me know if this is the variation you're looking for? Thanks
      Chandoo - happy to share, if interested.

      • Chris Macro says:

        Hey Tyler - Thanks for shooting that file over to me! I actually was speaking hypothetically but it was great to see my thoughts take physical form. I definitely think it would be worth sharing with everyone 🙂

      • prasad says:

        Hi all,
        We use similar tracker at our company. Its very difficult to remember to stop time tracker after completing task, can we give coding for start & stop button to function at given time?
        Eg: I started time tracker at 3:00 pm I know it will take 1 hour to finish the work, so accordingly can I tell excel to stop tracker (automatically hit stop button) at 4:00 PM?

        This way I don't need to remember to start or stop my tracker, basically I want to function tracker on its own at certain time.

  2. GJ says:

    Sweet! For the next project, let's build a time machine 🙂

  3. Excellent! Thanks for sharing!

  4. Vishy says:

    I just modified the code to display timer

    Sub startStopTimer()
    If Range("timer.button.label") = "Start" Then
    Range("time.stamp.start").Offset(Range("count.of.timestamps") + 1).Value = Now
    Range("timer.button.label") = "Stop"
    looptime

    Else
    Range("time.stamp.start").Offset(Range("count.of.timestamps"), 1).Value = Now - Range("time.stamp.start").Offset(Range("count.of.timestamps"))
    Range("timer.button.label") = "Start"
    End If
    End Sub

    Sub looptime()
    While Range("timer.button.label") = "Stop"
    Range("timer").Value = Now - Range("time.stamp.start").Offset(Range("count.of.timestamps"))
    DoEvents
    Wend
    End Sub

    Also .. cassigning any key press to the macro .. this will be cool

  5. Russ says:

    Chandoo,
    As a consultant often working from home, sometimes it I forget to note the time when I start working for a client or when I leave off for a break or an interruption. I think this timer is going to be a great tool.
    I have added it to the Windows Start Menu Folder so I don't have to remember to open it; I added a Comments field beside the time to record what I was working on; I added a total time so I can see how much time I have spent in the day.
    I have added separate sheets for each client, and I did not even have to make versions of the macro or named ranges - Excel handles that automatically.
    I also like the fact there is no timer running in the background - you can even close the file (remembering to save it) and when it is opened later the Stop will still record the correct elapsed time. The simplicity of the solution is its greatest feature.
    Congrats, and thanks.

  6. […] created a timer, to keep track of the time it takes him to solve a Rubik’s Cube puzzle. You could use it for work-related tasks […]

  7. Tapan says:

    Hi Chandoo,

    don't know why but it is such a simple macro and I wanted to use to keep track of my time learning VBA , but I am not able to assign the rounded rectangle button, go to formula bar and type =timer.button.label it comes up reference error and also when I am trying to use the CountA function is not working when I try to copy the code line to line as per your instruction. I downloaded your file and one thing that I have noticed that when I give names to selected ranges those names do not appear in my spreadsheet but it does on yours , is it because I am using 2013 version? your help will be greatly appreciated.

    cheers,

    tapan

  8. Roan says:

    Nice work Chandoo - this is a great example of a VBA project to give those just starting out in the coding area so that they can gain confidence and see fast results. And it has lots of opportunity for upgrades as people get more adventurous. The examples above are testament to that.
    It was interesting for me to note that when using Now() in a cell, it gives precision to milliseconds, while when using it in VBA, the precision was only to seconds. For those of us pedantic enough to want to see it down to milliseconds, the code below is the closest I could come (I also included a counter and displayed the Stop date and time, hence the additional columns):

    Sub TimerTracker()

    Dim dDate As Double
    Dim sMilliseconds As Single

    If [Start.Stop.Indicator] = "Start" Then
    [Start.Stop.Indicator] = "Stop"
    [Heading.First].Offset([Count.Entries] + 1) = [Heading.First].Offset([Count.Entries]) + 1
    dDate = Now()
    sMilliseconds = Timer
    sMilliseconds = sMilliseconds - (dDate - Int(dDate)) * 86400
    dDate = dDate + sMilliseconds / 86400
    [Heading.First].Offset([Count.Entries], 1) = dDate
    Else
    [Start.Stop.Indicator] = "Start"
    dDate = Now()
    sMilliseconds = Timer
    sMilliseconds = sMilliseconds - (dDate - Int(dDate)) * 86400
    dDate = dDate + sMilliseconds / 86400
    [Heading.First].Offset([Count.Entries], 2) = dDate
    [Heading.First].Offset([Count.Entries], 3) = _
    [Heading.First].Offset([Count.Entries], 2) - [Heading.First].Offset([Count.Entries], 1)
    FormatDuration ([Heading.First].Offset([Count.Entries], 3))
    End If

    End Sub

    And format the duration so that only the necessary time periods are shown (yes, that pedantic nature coming out again!):

    Sub FormatDuration(Duration As Range)

    Select Case Duration.Value
    Case Is < 0.000694444 'seconds
    Duration.NumberFormat = "s.000""s"""
    Case Is < 0.041666667 'minutes
    Duration.NumberFormat = "m""m ""s.000""s"""
    Case Is < 1 'hours
    Duration.NumberFormat = "h""h ""m""m ""s.000""s"""
    Case Else 'days
    Duration.NumberFormat = "d""d ""h""h ""m""m ""s.000""s"""
    End Select

    End Sub

    And a small clear routine should you want to start again:

    Sub ClearTracker()

    If [Start.Stop.Indicator] = "Stop" Then
    MsgBox "Please stop the timer before clearing (click on the 'Stop' button)", vbOKOnly + vbCritical, "Timer Tracker Clearing"
    Exit Sub
    End If

    If MsgBox("Are you sure you want to clear all the times in the tracker?" & Chr(10) & _
    "Click 'Yes' to clear or 'No' to exit without clearing.", vbYesNo + vbInformation, _
    "Time Tracker Clearing") = vbYes Then
    [Heading.First].Offset(1).Resize([Count.Entries], 4).EntireRow.Delete
    End If

    End Sub

  9. Valeria says:

    I don't know why I kept getting an error, however I am using your template to easily track time for invoices... it rocks! Thanks a lot ^__^

  10. Tom R says:

    This is fantastic! I followed the post about creating individual sheets - Great. I am learning VBA and as such, I have a Question:
    I need to track multiple items on the same spreadsheet, some simultaneously, for a "Daily" sheet. Then I will create multiple sheets for the workdays.
    Is it possible to have a timer for each line item?

  11. Dan says:

    First, this product is exactly what I have been looking to create. My question is how can I put more than one on a tab. I am using this to track how long someone works on different steps of a process map. Therefore I'd like them to start and stop each step along the way. This would require one of these tables every few columns over (maybe 6-7 timers per sheet) Please let me know if you can help.

    thanks!

  12. Sayli says:

    Great post- however I was trying to build a timer that counts to millisecond accuracy. Here is a link to a sample timer. I used the tricks from both timers to create what I needed. Thank you for your great tutorials on excel. They are a big help!

    http://www.mrexcel.com/forum/excel-questions/428321-millisecond-accuracy-excel-timer.html

  13. Jarrod says:

    Can someone help me to create this into a a timer that tracks total steps to a process i.e. a patient that checks in (start/Stop/lap), then called back by the nurse to the room (start/Stop/lap), Provider time, etc. Maybe use a key to start and stop too???

  14. Chis says:

    I enjoyed your example of the timer and have been using it for a project I am working on at the moment to track machine downtime.

    Is it possible to have the button change color when the timer is running (green) versus not running (red). I have tried to do this myself but with no success.

    Appreciate your feedback

  15. Keith says:

    Hi. Just discovered your site here. You do great work.
    I'm trying to use your timer shown above (click Start and it enters timestamp into table & changes Start to STOP button. click again, it stops and calculates duration.

    I can't get it to work.
    1. I don't see how you get the start button to change to stop.
    2. when I enter your code in VB one of 2 things have happened.
    either I type it as seen on this page or I copy and paste. Copy will have more code info than retyping what is above. I'm guessing I'm missing other lines too.

    Could you please make sure everything is shown. I could sure use this timer.
    Thanks

Leave a Reply