How to insert date in Excel?
Inserting the date in an Excel sheet is a necessary operation that every Excel user may require while working with Excel sheets. There are several ways in Excel to insert the date in an Excel sheet. Excel users can insert the date manually or use Excel inbuilt functions to insert the date in a worksheet.
Excel allows the users to use different date formats, such as 01/01/2020, 01-01-2020, 15-May-2021, etc. Including these, several other formats are allowed to insert into the Excel sheet.
Date format
A date has several formats. Excel allows the users to use these formats and store the date in those formats. The Excel users need to select the format in which they want to show their inserted date data.
A date can be static or dynamic in nature. A static date is a hard-coded date, i.e., manually entered by the user as in the below topic of this chapter. This date does not change automatically until a user changes it.
On the other side, a dynamic date is inserted by using an Excel function like TODAY() that returns the current system date. It changes in real-time automatically by taking the date from the system on which the Excel sheet is opened.
Manually entered date in Excel
By default, Excel uses the dd-mm-yyyy format for date. You can change the format to date format via Format Cells feature of Excel. Excel does not support the backslash (/) operator to separate the date. If you enter the date like 05/05/2020, it will automatically change to 05-05-2020 date format
We had manually entered some dates in an Excel column. These dates are in different formats. These are entered by typing the date manually in the cell. Following are some dates entered manually in Excel cells.
These are the different date formats supported by MS Excel. All the dates are right-aligned in this Excel sheet. All these data are in date format only. If the date entered by the user is acceptable as date by Excel, it will be right-aligned in the cell.
On the other side, if the entered date is not a proper date and not acceptable as date by the Excel, it will be left-aligned in the Excel cell. It means that the entered date is in text format rather than date. If Excel fails to recognize your input as a date, it is left-aligned.
Insert current date and time
In the above screenshot, all dates are entered manually by typing in an Excel sheet. Now, we will use Excel inbuilt function to enter the current date and time. This time is updatable in real-time. TODAY() function will return the current date of the system.
TODAY() – Return current date
- Write the following function in an Excel cell and then hit the Enter key to get the current date.
=TODAY()
- See the result (current date) returned after using the above function.
Next day, when you will open this Excel sheet, this resultant date will be changed automatically. You can try it on your system.
Besides this, there is one more function in Excel using which you will find the current date, i.e., NOW(). But this function returns the current time along with the current date.
NOW() – Return current date and time
- Write the following function in an Excel cell and then hit the Enter key to get the current date.
=NOW()
- See the result (current date and time) returned after using the above function.
When to enter date manually?
You can enter date manually while entering DOB, date of joining when you do not need the automatic updation in date. When you want the date to be updatable in real-time fetched from the system, you can use either TODAY() or NOW() function.
Shortcuts to enter current date and time –
| Command | Explanation |
|---|---|
| CTRL + ; | You can use this shortcut command to enter the current date in an Excel cell. |
| CTRL + Shift + ; | Use this shortcut command to insert the current time in an Excel cell. |
To insert both current date and time, use CTRL+; and then press Space key after that CTRL + Shift + ;. Both date and time will be inserted.
Auto-populate dates in Excel cells
Let’s take a scenario that you are making the monthly report at the end of the month. You need to enter the date from month start to month-end. If you enter the date one by one in each cell, it will take too much time to insert date 30th times.
We can auto-populate the date and save time. For this, we will use the Excel autofill feature. See the steps to auto-populate the date in an Excel sheet –
Step 1: Enter the initial date in the first cell of the Excel sheet.
Step 2: Now, select the entered date of the cell and take the cursor at the bottom right corner of the cell where + sign will appear.
Step 3: Drag this + symbol to the below cells till date you want to insert the date. We have dragged it to row 30 and auto-populated the date till the month-end.
Change the date format
Excel users can choose different date formats offered by Microsoft Excel. This feature is available inside the Format Cells in the Home tab. Several date formats are available and you can also choose custom date formats, like date with time, date with day, etc.
Step 1: To change the format of the inserted date, first of all, select the date and right-click on it.
Step 2: Choose the Format Cells option from the list that will open a formatting window panel.
Step 3: You are currently on the Date tab, where dd-mm-yyyy is the current date format.
Step 4: Choose a date format in whatever form you want to display your selected date and click OK.
Step 5: See that all the selected date has been changed to the selected format.
Insert date using DATE() function
DATE() is another inbuilt function of Excel using which a specific date can be inserted in a valid format. In this function, you do not need to specify any date format. You have to just insert the year, month, and day as a parameter in this function to get the date.
Following is the syntax of the DATE function –
Provide the year, month, and day in the function separated by a comma and press the Enter key to get the result.
See the given step to insert the date using the DATE() function in the Excel sheet.
- In an Excel cell, enter the following formula.
=DATE(2019, 10, 24)
- Hit the Enter key and see the returned result in the sheet. The date is automatically formatted into the dd-mm-yyyy format.
Insert date using Excel UI
If the user is not aware with the Excel functions/formulas and does not know how to use these functions, users can use the Excel user interface to insert the date using functions.
The Formula tab of Excel contains a list of all formulas offered by Microsoft Excel. The Excel functions are categorized into multiple categories: Financial, Logical, Text, Date & Time, and more. You can use TODAY(), NOW(), DATE(), and other functions to insert the date in the Excel sheet.
Go to a cell and select a function like TODAY() from the Date & Time dropdown list under the Formula tab.
A dialogue box will appear before inserting the date in the Excel sheet. On this, click OK.
The current system date has been inserted into the selected cell.
