A Great Way to Create an Excel Micro Timer

Professionals who are used to organizing their lives in Excel at some point wish Excel could include a timer to help keep track of common tasks. Tasks like tracking time spent on specific projects or monitoring hourly rate. A quick look, however, reveals that – unless you are an accomplished VBA programmer – Excel does not offer an easy Start/Stop timer feature.

Excel does however offer features for storing and working with time data. If we put a few of these familiar tools together, we can create something very close to a Start/Stop timer without resorting to VBA.

This example will create a simple Time Log that:

• Inputs Start and Stop time with the press of a button, and then
• Calculates the elapsed time automatically.

To get started, create a Table with the three columns you’ll need: Start Time, Stop Time, and Total Time. Format the cells in each column to the Time format.

To follow using our example, download Time Tracking Worksheet

Then, create a Macro that will enter the current time into a cell. The time used will be based on the clock time of the computer running Excel:

1. Put your curser in the cell you want the time entered.
2. Click Record Macro on the Developer tab.
3. Enter a name for the macro and a description in the Record Macro dialog box, then click OK.
4. Hit CTRL+Shift+; (semi colon)

This is the shortcut key for entering the time. You’ll get something like this:

5. Click Stop Recording.

This gives us a starting place, but we’ll need to edit the macro a bit to get it to work the way we want. To edit the macro:

1. Click Macros button on the Developer tab.
2. Select the macro, then click Edit to open the VBA editor.
   
3. This is the code the macro recorded:
   ActiveCell.FormulaR1C1 = “1:22 PM”

Range(“A2”).Select

If left, this would simply put the text “1:22 PM” into any cell the macro runs on. Instead, we need to replace “1:22 PM” with the NOW function that tells Excel to enter the current time. Replace the above with:

ActiveCell.Value = Now()

4. Hit CTRL+S to save the macro changes and the sheet.

Now, when you run the macro, it will insert the current time into the active cell.

Next, let’s assign the macro to a button so you can record your Start/Stop times with a simple click:

1. On the Developer tab, click the Insert dropdown button.
2. Choose the Button icon from the Form Controls list.

3. Drag the cursor to outline the size and shape you’d like your button.
4. Select the macro from the list in the Assign Macro dialog box. And click OK.

5. Right-click on the new button and select Edit Text to change the button’s label

Ta da! Now you have a quick way to enter time into your spreadsheet without typing a command or copy/pasting from another stopwatch tool on your desktop. Once the times are in the table, you can work with them in any way you might need. Our example Total Time subtracts Start Time from End Time to calculate elapsed time.

HINT: Set the format for the Total Time cells to type 13:30:55 in the Format Cells dialog box, or type 37:30:55 if your time totals ever total more than 24 hours.

Once your excel macro “timer” is created, you can begin integrating Start/Stop times into your spreadsheets. Create any number of formulas to help you track your time, manage projects, and maintain budgets.

Or, you can chart elapsed times to help you improve scores.

Now it’s your turn: Go get creative! Just make sure to keep track of your time.