Copyright infringement

How to add a timestamp to an Excel record

Like many Excel tasks, there is an easy way and a better way to enter a timestamp for your records. Learn about both in this article.

excel-vector.jpg

Photo: 200dgr / Shutterstock

Many applications use a timestamp to determine when a log was entered or a project was completed. They are common and useful when you need to know when something is going to happen. There are several ways to get the job done, but be careful. There is an easy road, a bad road, and then an automatic road. In this article, we will review all three and you can decide whether to use the easy method or the automation. You’ll also learn why you don’t want to use the bad method – one that probably looks workable from the top of your head, but isn’t.

We see: 69 Excel Tips Every User Should Master (TechRepublic)

I’m using Microsoft 365 on Windows 10 64-bit, but you can use earlier versions. The online version will not support macros. You can download demo files .xlxs, .cls, . xls or work with your own data.

What is a timestamp

It is not easy to define the term timestamp because its definition depends on its use. Simply put, it’s the current date and time used to determine when something happened. This could be the time the log was entered, the time the project was completed and so on.

Use the built-in hotkeys

If you’re familiar with Excel, you probably know that you can use some shortcuts to enter both the time and date:

  • date: Ctrl +;
  • time: Ctrl + Shift +;

If you want both values ​​in the same cell, you can still use these shortcuts. Enter the first and without pressing Enter – while still in edit mode – enter a space and press the second shortcut. The result is a custom format that displays the date and time. Figure A The result of using these shortcuts appears. Excel not only enters the date and time but also formats the cells for display purposes.

Figure A

exceltimestamp-a.jpg

Use the built-in shortcuts to enter the date and time.

There’s nothing wrong with using hotkeys – they work, and if you don’t mind keystrokes, this is an easy fix. The downside of course is that you have to remember to turn on keystrokes. Combining the two into one cell is probably not a good idea unless your users are specially qualified; They may not remember to use both, and may conflict with the selector that separates the two values.

We see: Windows 10: Voice Command Menus for Speech Recognition and Dictation (Free PDF) (TechRepublic)

I usually prefer the easiest solution, and keystrokes may work for you. On the other hand, it’s probably not a great solution to pass it on to your users.

Why NOW() and TODAY() won’t work as timestamps in Excel

Some of you may think that the NOW() and TODAY() functions will get the job done, but they won’t – not easily. NOW() returns the current date and time and what is displayed depends on the format. TODAY() displays the current day.

Excellent isn’t it? No, it’s fickle jobs. This means that both functions will be updated every time the sheet is computed, so they are far from perfect. You can convert the results to constant values, but this takes a lot – a lot of work.

How to use a macro to timestamp in Excel

The surest way to get a valid timestamp that remains valid and consistent is to use a macro, or rather a user defined function. The code is simple. The biggest issue is when to run. For our purposes, we’ll add the macro to the Quick Access Toolbar. The user simply clicks on it when they are ready to add the timestamp.

We see: Microsoft may release Windows 10 for desktop as a service this summer (TechRepublic)

sub procedure in Menu A Short and sweet. With the selected cell – which is in the user’s hand – this code enters the formatted NOW() function that displays the date and time. Depending on your needs, you may choose to use TODAY() or format the cell differently.

Menu A

sub timestamp()

Enter the current date and time, in m/d/yyyy format h:mm:ss AM/PM.

The user will select the cell and then click the macro button in QAT.

with selection

.Value = Now

.NumberFormat = “m/d/yyyy h:mm:ss AM/PM”

ended with

End Sub

To enter the macro, do the following:

  1. Press Alt + F11 to open the Visual Basic Editor.
  2. In the project editor (to the left), select the ThisWorkbook module.
  3. Enter the code in the module (Figure B). Do not paste the code from this webpage into the module because it will not work. You can import the .cls file included in the download or enter the code manually. You can also paste the code into a text editor and then copy it into the module – it should work. The problem is with dummy web characters that are pasted with plain text.
  4. Return to Excel and save the workbook as a macro-enabled macro. If you are using an existing version, you can skip this step.

Figure B

exceltimestamp-b.jpg

Enter the sub-action.

Let’s run the action from the Developer tab, just to make sure it works. To do this, click on the Developer tab, and do the following:

  1. Click the cell where you want to enter the timestamp.
  2. On the Developer tab, click Macros in the Code group.
  3. In the resulting dialog, choose Action in the Macro Name control (Figure C), then click Run. figure d Displays the resulting timestamp.

Figure C

exceltimestamp-c.jpg

Run the sub-action.

figure d

exceltimestamp-d.jpg

Enter the timestamp sub-action.

You don’t want to run the action this way every time you want to enter a timestamp, so let’s automate this process a bit. Specifically, we will add the action to QAT, so all you have to do is select the cell, click the button in QAT and continue with your work. (The menu version does not support QAT, but you can set a shortcut keystroke.)

To add the macro to QAT, do the following:

  1. Click the QAT dropdown menu (the arrow to the right of QAT) and choose More Commands.
  2. From the Choose Commands From drop-down list, choose Macros.
  3. There is only one in the demo workbook, but if you’re working with your own, make sure you specify the correct macro: ThisWorkbook.TimeStamp.
  4. Click Add to move it to the QAT list on the right (Figure E).
  5. Click OK to return to the sheet. You will see the macro icon on QAT (shape and).

Figure E

exceltimestamp-e.jpg

Add the macro to QAT.

shape and

exceltimestamp-f.jpg

Click the macro button.

you are done! Select any cell and click the macro icon in QAT to enter a timestamp. You can add the username, change the format, or just enter the date. If encouraged, you can create a parameter-based action that allows the user to quickly select these items. As it stands, this is a solution that users won’t mind using and will return consistent values.

see also