The Date and Timestamp is a type of data type that determines the date and time of a particular region. It contains some characters along with some encoded data. This format may vary from language to language. Keeping track of date and time helps in managing records of our work as well as segregate the information day-wise. In this article, we will be going to learn how we can automatically insert Date and Timestamp in Excel.
There are multiple ways to insert Date and Timestamp in Excel. These methods include both static and dynamic methods.
1. Inserting Date And Timestamp using keyboard shortcuts (Static method)
The static method is helpful when there are only a few cells where you need to insert the Date and Timestamp. You can quickly use the keyboard shortcut to manually insert the Date and Timestamp.
Follow the below steps to implement the same:
- Select the cell into which the current date or time needs to be inserted.
- Use this shortcut – Ctrl + ; (Control + semicolon) to insert the current date.
- Use this shortcut – Ctrl + Shift + ; (Control + Shift + semicolon) to insert the current time.
- Use this shortcut – Press the combination (Ctrl + ;) and (Ctrl + Shift + ;) to insert the current time and time.
2. Inserting Date And Timestamp using Formulas:
The NOW() and TODAY() functions can be used to insert the current date and time that will update automatically. This method dynamically updates the date-time whenever a change is made in the worksheet.
- NOW(): To insert current date and time.
- TODAY(): To insert the current date.
Follow the below steps to implement the same:
- Select the cell into which the current date or time needs to be inserted.
- Enter the function NOW() or TODAY().
- Press the Enter key.
- The current date-time or date will be inserted into the selected cell.
3. Insert Timestamp Automatically While Entering Data In Different Column
This method will enable the insertion of the timestamp in a column while entering data into another column. For example, consider two columns A and B in your worksheet. While you enter data in a cell of column A, the current date and time in the corresponding cell in Column B will get updated automatically.
Follow the below steps to implement the same:
- Click File -> Options and the Excel Options dialogue box will appear. Now from the left pane, select the Formulas option. You will see Enable interactive calculation in the right pane below Calculation options. Check this option and select OK.
- In this next adjoining column (say column B), enter this formula:
=IF(A1<>"",IF(B1<>"",B1,NOW()),"")
- Drag and select cells to auto-fill the formula.
- You can also customize the format of the date and time. To do this, right-click on the selected formula cells, go to the context menu, and select Format Cells. You will see the Format cells dialogue box. Go to the bottom and select Custom. In the right pane select the suitable format for your cells.
- Now when you enter the data in a column, you will get the date and time in your chosen format in the adjoining cell.
4. Using VBA To Insert Timestamp Automatically While Entering Data In a Different Column
If you are familiar with working on VBA, then there is another method for you to automatically insert the timestamp in your excel sheet using VBA.
Follow the below steps to implement the same:
- To open Microsoft Visual Basic for Applications, press Alt + F11. The VBA window will open. Now go to Insert and select the Module to open a blank module.
- Now, add the code given below to your blank module and save the code.
Function MyTimestamp(Reference As Range) If Reference.Value <> "" Then MyTimestamp = Format(Now, "dd-mm-yyyy hh:mm:ss") Else MyTimestamp = "" End If End Function
- Go back to your worksheet and type the below formula in your cell in which you want to insert timestamp.
A is the column for inserting entries of data. B is corresponding column, into which the date and timestamp will be updated. Type the below formula into B1 cell: =MyTimestamp(A1)
- Now, if you insert the entry in a column, you will get the date and time in the adjoining cell automatically.