Excel provides a few different ways to add timestamps to cells. The most straightforward method is to use the built-in NOW() function, which returns the current date and time. You can also use keyboard shortcuts to quickly insert the current date or time into a cell. Additionally, Excel has a TIMESTAMP function that allows you to convert text timestamps into Excel date/time values. Read on for more details on how to use timestamps in Excel.
Using the NOW() function
The easiest way to add a timestamp in Excel is with the NOW() function. NOW() returns the current date and time according to your computer’s clock. For example, if you enter =NOW() into a cell, it will display a datetime like 1/1/2020 12:15:45 PM.
The NOW() function will update whenever the worksheet recalculates, so the timestamp stays up-to-date. This makes it useful for tracking things like when a worksheet was last updated or when an order is placed. To insert the current date and time using NOW():
| 1. Select the cell where you want the timestamp. |
| 2. Type =NOW() |
| 3. Press Enter |
By default, NOW() returns both date and time. You can format the cell to display only the date or time component using Excel’s Date and Time formats.
Updating timestamps with NOW()
The NOW() function recalculates whenever the worksheet is changed, so the timestamp stays updated. You can also force a timestamp to update by pressing F9 to refresh the sheet, or enable automatic calculation under Formulas > Calculation Options. If you don’t want the timestamp to change once entered, you can convert the formula to a value:
| 1. Select the cell with NOW() |
| 2. Copy the cell (Ctrl+C) |
| 3. Paste just the value back into the cell (Paste Special > Values) |
This will convert the formula into the current date/time value.
Inserting dates and times with keyboard shortcuts
Excel provides keyboard shortcuts to quickly insert the current date or time into a cell. This can be faster than typing the NOW() formula.
To add the current date, use Ctrl + ; (semicolon).
To add the current time, use Ctrl + Shift + : (colon).
This will insert the date or time as a static value, not a formula. The value will not update automatically like NOW() does. You can format the cells using the Date and Time formats to show only the parts you want.
Date and time shortcuts
| Ctrl + Shift + : | Insert time |
| Ctrl + ; | Insert date |
These shortcuts provide a quick way to add timestamps that don’t need to update. For timestamps that should stay current, use the NOW() function.
Converting text timestamps with TIMESTAMP
You may have data with timestamps stored as text rather than Excel dates. For example, data imported from another source may have datetimes like “1/1/2020” or “1/1/20”. The TIMESTAMP function converts these text strings into Excel’s datetime format.
To use TIMESTAMP:
| 1. Enter the timestamp text string into a cell |
| 2. In another cell, use =TIMESTAMP(A1) where A1 is the cell with your text |
| 3. Format the cell with TIMESTAMP as a date or datetime |
This converts the text into a datetime serial number that Excel can understand. You can then use the converted value in date calculations or graphs.
TIMESTAMP examples
| Data | Formula | Result |
| “1/1/2020” | =TIMESTAMP(A1) | 1/1/2020 12:00:00 AM |
| “1/1/20” | =TIMESTAMP(A1) | 1/1/2020 12:00:00 AM |
| “Jan 1, 2020” | =TIMESTAMP(A1) | #VALUE! error |
The date formats need to be unambiguous – TIMESTAMP doesn’t recognize text strings like “Jan 1, 2020”. Overall, the TIMESTAMP function provides a handy way to convert date and time stamps from text into Excel’s datetime format.
Using VBA to create dynamic timestamps
In addition to Excel formulas, you can use VBA macros to insert dynamic timestamps. This allows more flexibility to create timestamps that update based on triggering a macro.
Here is an example VBA function to insert a timestamp anytime a cell value changes:
Function AddTimestamp(TriggerCell As Range)
If TriggerCell.Value <> TriggerCell.OldValue Then
Application.EnableEvents = False
TriggerCell.Offset(0, 1).Value = Now()
Application.EnableEvents = True
End If
End Function
To use this:
| 1. Enter data in a cell that will trigger the timestamp (TriggerCell) |
| 2. In the adjacent cell, use the formula =AddTimestamp(A1) where A1 is the trigger cell |
Now anytime the data in A1 changes, it will add the timestamp in the adjacent cell. This provides dynamic timestamps that can update based on cell changes.
More VBA timestamp options
Other examples of timestamp macros in VBA:
- Insert timestamp on a worksheet when any change is made
- Add timestamp to a cell when a specific value is entered
- Insert timestamps based on events in a userform
- Add timestamps to a range of cells with a macro
VBA provides extensive options for adding and controlling timestamps. So if you need more advanced timestamp features than the Excel formulas provide, VBA macros can help.
Formatting date and time stamps
When you insert timestamps in Excel, you will likely want to format them to show only the parts you want. Excel provides extensive formatting options for dates and times:
- Date formats – show dates in different styles like 1/1/2020, January 1, 2020, or 2020/Q1.
- Time formats – display times in various ways like 3:15:45 PM or 15:15.
- Custom formats – create custom date/time styles like Wed, 1 Jan 2020.
To format timestamps:
| 1. Select the cells with timestamps |
| 2. On the Home tab, open the Format Cells dialog |
| 3. Go to Date or Time section and pick the desired format |
| 4. You can also create custom formats at the bottom |
Properly formatting dates, times, and timestamps is just as important as inserting them into your worksheets. Take the time to apply the formatting so the timestamps display your preferred way.
Displaying date and time components
When using the NOW() function, you can display only the parts of the date and time you want by formatting the cell:
| Format | Display |
| d/m/yyyy h:mm AM/PM | 1/1/2020 1:23 PM |
| dddd, mmm d | Thursday, Jan 1 |
| m/d/yy | 1/1/20 |
| [h]:mm:ss | 1:23:45 |
Customize your timestamps by displaying only the parts you need.
Using timestamps for data validation
Timestamps are useful for tracking activity in your worksheets and workbooks. Here are some examples of using timestamps for data validation and auditing:
- Track when transactions occur by adding timestamps to a data entry log or system record
- Validate data by checking if transactions fall within expected time periods
- Calculate durations by referencing cell timestamps
- Use timestamps to track workbook edits and revisions
- Add timestamps when files are created or modified through VBA
By leveraging timestamps strategically, you can better track activity, validate data, and improve auditing of your Excel workbooks.
Timestamping data entries
Here is an example of using timestamps to track data entries in an order system:
| Order ID | Customer | Order Date | Ship Date | Delivery Date |
|---|---|---|---|---|
| 100 | John Smith | 1/1/2020 1:23 PM | =IF(C2=””,””,NOW()) | =IF(D2=””,””,NOW()) |
Formulas add timestamps when orders are shipped and delivered. Management can use these timestamps to calculate shipping times, validate delivery dates, and more.
Conclusion
Timestamps are invaluable for recording activity dates and times in Excel. The key methods for adding timestamps are:
- The NOW() function to insert current date and time
- Keyboard shortcuts Ctrl+; and Ctrl+Shift+: for quick date and time stamps
- TIMESTAMP function to convert text strings into Excel datetime values
- Custom VBA macros for advanced timestamp needs
Proper formatting brings out the timestamp components you want to display. Strategic use of timestamps facilitates data auditing, validation, and analyzing workbook activity over time.
In summary, Excel provides flexible options to add timestamps through formulas, shortcuts, VBA, and formatting. Employing timestamps helps track activity, record data chronologically, and improve analysis.