There are several purposes for inserting dates in Excel; tracking any information or for any date-time calculation you need to insert dates. Today we are going to show you various ways to insert date in Excel. For this session, we are using Excel 2019, feel free to use yours.
Here you can see there are several features like keyboard shortcuts or functions to insert dates. We will try to demonstrate all of these in the following sections.
Note that our data is a compact one that we have changed depending on the context of examples.
You are welcome to download the practice workbook from the link below.
Insert Date in Excel
1. Keyboard Shortcuts to Insert Date
Let’s get to know about the keyboard shortcuts that insert dates in Excel.
To insert the date the shortcut will be CTRL + ; (semicolon)
Let’s hit the keys in Excel.
We are preparing this tutorial on September 28, 2021. The shortcuts provided us with the current date.
If you want the current time along with the date, then the keyboard shortcut will be
CTRL+; (space) CTRL+SHIFT+;
SPACE works as the delimiter that separates the date and time value. Use the keys, you will find the current date and time.
It was 4:11 PM in our time zone when we used the shortcut keys.
Note that you need time only, then use CTRL+SHIFT+;
2. Basic Excel Functions to Insert Date
Excel provides several built-in functions to insert dates. Let’s explore them.
I. TODAY Function
We can use the TODAY function to insert dates. As the name suggests, TODAY returns the current date. To know more about the function, visit the TODAY function.
Let’s use the function.
Using this function, we have found the date (the current date).
One thing to note, this function changes the date automatically. If you open the workbook a couple of days later you will find the date of that day. For a better understanding, you can visit this article regarding automatic change of dates.
II. NOW Function
You can use the NOW function to insert dates. It will also provide the current date, in addition to that, it returns the current time. Check this NOW article for further information.
Now, let’s use the function.
By the time, we have used the function, it’s 5:02 PM here. It provides the current date and time. This value will also be changed automatically.
3. Insert date using DATE Function
To insert dates we can use another function called DATE. The DATE function forms a valid date from an individual year, month, and day component. To know more about this function, visit this article: DATE.
Let’s use the function where we will provide day, month, and year values manually.
Here, 2021 is the year, then 9 and 28 are the month and day respectively. We will find the date in the format showing in the following picture.
Note that you can use any other format to represent the date. Open the Format Cell dialog box (shortcut: press CTRL + 1)
You will find different date formats there. Use your preferred one. For the time being, we are leaving it as it is.
For inserting dates, we can combine the DATE function with TODAY. The formula will be in such a way that DATE will fetch the year, month and day, produced from TODAY.
Now, let’s explore the formula
The YEAR fetches the year value from the result of TODAY. And the MONTH and DAY fetches the month and day value respectively from the result of TODAY.
Then the DATE function returns the date.
Note that, the date from this formula will change automatically. You can use NOW instead of TODAY but that will increase complexity to the operation.
4. Exercise AutoFill Feature
We can use the Excel AutoFill feature to insert dates. First of all, insert any date you want.
We are going with the current date (September 28). Selecting the cell you will see the AutoFill Handler at the bottom right side. Clicking it drag down to the cell you want to insert dates.
Here we have found the following dates.
5. AutoFill Dates with Intervals
In the earlier section, we have auto-filled dates without any interval. If you want dates after a regular interval, then selecting the dates (what we have inserted using AutoFill) click Series from Fill option in the Editing section of the Home tab.
Series dialog box will come in front of you. Set your desired interval value in the Step Value field there.
Here we have used 2 as Step Value. Click OK, it will provide every second date from the previous one.
Another quick way to do this is to insert two dates having an aperture.
Now use the AutoFill feature.
You will find dates after a regular interval.
6. Insert Date Depending on Adjacent Cell
We can form a formula that will help us insert dates depending on the adjacent cell. For showing the example, we will use the following dataset.
Once we insert “Yes” in the Purchased column we will find the date in the Date column.
Our formula will be the following one
We have used the IF function to execute condition-based time insertion. Read this IF article to know about this.
We have checked whether C4 is having “Yes” or not. If there is “Yes”, then check whether D4 is empty or not. For being empty we set NOW for the cell.
Drag the formula to the next cells of this column, so the operation can be performed there as well.
We have inserted “Yes” in another couple of cells from the Purchased column, for that cells, we found the date-time. No dates for empty adjacent cells.
7. Update Date-Time Upon Changes in Adjacent Cell
We can build a formula that will insert and update dates (and times) whenever we update the adjacent cell.
We will use ADDRESS, CELL functions to create the formula. ADDRESS returns the address for a cell based on a given row and column number and CELL returns information about a cell in a worksheet.
Our formula will be the following one
This formula uses the CELL function to get the reference of the last edited cell, and if it’s the same as the one to the left of it, it updates the date-time value.
Let’s change the value from B4 cell.
We didn’t change the date, but you can see the change of time.
Drag the formula for a couple of rows.
Let’s populate the adjacent cells (Input column), you will find the date-time value.
That’s all for today. We have listed several methods to insert date in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to comprehend. Let us know any other methods that we have missed here.
- How to Combine Date and Time in One Cell in Excel (4 Methods)
- How to Change Dates Automatically Using Formula in Excel
- Excel Automatically Enter Date When Data Entered (7 Easy Methods)
- How to Insert Dates in Excel Automatically (3 Simple Tricks)
- How to auto populate date in Excel when cell is updated