How to Automatically Insert Date and Timestamp in Excel?
Last Updated :
27 May, 2021
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.
Similar Reads
How to Create Slicers and Timelines in Excel? Slicers and Timelines in Excel are used to implement with the Excel tables and Pivot Tables to help in filtering out large amounts of data to get the high level of information from a collection of data. Finding Slicer and Timeline in Excel Navigate to the Insert menu ribbon, Select the Filters secti
3 min read
How to Create Automatic Rolling Months in Excel? Microsoft created the spreadsheet Excel for Windows, macOS, Android, and iOS. Excel offers calculation, computation, visualization tools, pivot tables, and VBA. You will find a lot of cells in a spreadsheet. The names of the rows and the columns are both written in alphabetical order. The row and co
3 min read
How to Expand Cells to Fit the Text Automatically in Excel? We all know how useful Excel is to store tabular data. We can do calculations in excel, we can store any information that is in the form of tables, and so on. But there are some common problems that we all face while using Excel. One of the problems that we encounter while entering oversized, overle
3 min read
How to Insert and Run VBA Code in Excel? In Excel VBA stands for (Visual Basic for Application Code) where we can automate our task with help of codes and codes that will manipulate(like inserting, creating, or deleting a row, column, or graph) the data in a worksheet or workbook. With the help of VBA, we can also automate the task in exce
2 min read
How to Insert a Picture in a Cell in MS Excel? Every day in business or any other field lots of information are there that are required to store for future use. For anyone, it is very difficult to remember that information for a long time. Earlier data and information are stored in a form of a register, file, or by paperwork but finding it may b
4 min read
How to Insert Dates in Excel? In Microsoft Excel, the date can be inserted in a variety of ways, including using a built-in function formula or manually entering the date, such as 22/03/2021, 22-Mar-21, 22-Mar, or March 22, 2021. These date functions are typically used for cash flows in accounting and financial analysis. In Exce
4 min read
How to Remove Time from Date/Timestamp in Excel? Timestamp stores a combined Date and Time value. In this article, we will look at how we can create Timestamp and remove Time from Date in Excel. To do so follow the steps below: Step 1: Formatting data to create a timestamp. Select the cell, right-click on it choose Format Cells... Step 2: Then in
3 min read
How to Autofill Months and Years in Excel? Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android, and iOS. The calculation, computation, visualization tools, pivot tables, and VBA are all available in excel. In a spreadsheet, you will find a large number of cells. The columns are named in alphabets and rows are named in n
3 min read
How to Insert a Checkbox in Excel: Step-by-Step Guide How to Add Checkboxes in Excel: Quick Steps Enable the Developer Tab >>Go to Developer TabControl Group >> Click InsertSelect Checkboxes >>Place the CheckboxesLink the Checkbox to a CellCreating a checklist or adding interactive elements to your spreadsheet in Excel becomes much ea
8 min read
How to add timestamp to excel file in Python In this article, we will discuss how to add a timestamp to an excel file using Python. Modules requireddatetime: This module helps us to work with dates and times in Python.pip install datetimeopenpyxl: It is a Python library used for reading and writing Excel files.pip install openpyxltime: This mo
2 min read