Written by Allen Wyatt (last updated August 17, 2021)
This tip applies to Excel 97, 2000, 2002, and 2003
You may want to use Excel to record the elapsed time for different events. There are two ways that this can be approached: either native, within Excel, or with a macro.
If you don't want to use a macro, you can easily set up three columns for your timing. The first column can be used to record the start time, the second column the end time, and then the third column the elapsed time (calculated by using a formula that subtracts the start time from the end time). In order to record times, you select a cell in either the start time or end time columns and press Ctrl+: (the colon). Excel enters the current time in that cell.
If you want to use a macro that simply returns the elapsed time, then you can use the following:
Public Sub TimeIt() Dim vStartTime As Date vStartTime = Time MsgBox Prompt:="Press the button to end the timing" & vbCrLf _ & "Timing started at " & Format(vStartTime, "hh:mm:ss"), _ Buttons:=vbOKOnly, _ Title:="Time Recording Macro" ActiveCell.Value = Time - vStartTime End Sub
This macro records a start time (in vStartTime), and then displays a message box. When you click on the message box button, the difference between the current time and the start time is stored in the current cell. (You need to make sure the current cell is formatted with one of the time formats.)
The above macro works very well for recording short events during which you don't need to use Excel for other tasks. If you need to record longer events, then a different approach is in order. The following macros work in tandem. The first one records a start time; that is all it does. The second one uses that recorded time to calculate an elapsed time which is placed in the currently selected cell.
Global vStTime Sub StartTiming() vStTime = Time End Sub Sub EndTiming() ActiveCell.Value = Time - vStTime End Sub
You could easily assign these two macros to the Quick Access toolbar or to different toolbar buttons that would, respectively, start and stop the timing process.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2037) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Using Excel for Timing.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Work with times in a worksheet and you will eventually want to start working with elapsed times. Here's an explanation of ...
Discover MoreIf you need to determine the date of the last day in a month, it's hard to beat the flexibility of the EOMONTH function. ...
Discover MoreDon't want to use the EOMONTH function to figure out the end of a given month? Here are some other ideas for discovering ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2017-06-19 05:13:21
Helen
Great tip about the short cut for putting timing on a spreadsheet once I found it should be for me Ctrl. Shift and colon. I run a timesheet and did not realise this shortcut. Thank you.
2012-01-09 06:59:32
Dave Kerr
small typo...
Ctrl+: enters the date in the current cell.
Use Shift+Ctrl+: to enter the time.
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments