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 Excel, there is a built-in function called TODAY() that will insert the exact today’s date and will give the updated date whenever the workbook is opened. The NOW() built-in function can also be used to insert the current date and time, and this function will be kept up to date if we open the workbook multiple times.
Inserting the date:
In the Formula tab, the built-in TODAY is categorized under the DATE/TIME function.
Alternate to insert the date in Excel the below keyboard shortcut can be used:
It will insert the current date.
To insert the current date and time we can use the following shortcut keys:
CTRL+; <space key> CTRL+SHIFT+;
It returns the current date and time to us.
1. Inserting specific date in Excel:
We have to use DATE() to insert a specific valid date in Excel. We can notice in the above function that the DATE requests to provide Year, Month, Day values. If we provide the details, the default date will be shown as below:
2. Inserting static date and time:
A static value in a sheet does not change if the sheet is recalculated or opened. To do so follow the below steps:
Step 1: Select the cell in which the current date or time will be inserted on a table.
Step 2: Do one of the next:
- Press Ctrl+;(semi-colon) to insert your current date.
- Press the Ctrl+Shift+;(semi-colon) to insert the current time.
- Press Ctrl+;(semi-column) to insert the current date and time then press Space, and press Ctrl+Shift+; (semi-colon).
3. Inserting a date in Excel via a drop-down calendar:
It may be a good idea to include a down calendar in your worksheet if you set up a table for other users and want to make sure that the dates enter correctly. You can fill in the dates with a mouse click and be 100% confident that all dates are entered in a suitable format. You can use Microsoft Date Picker control when you use a 32-bit version of Excel. Microsoft Date Picker Control will not work when you are using a 64-bit Excel 2016, Excel 2013 version.
4. Inserting an automatically updatable today’s date and current time:
If you want to keep your Excel date up-to-date today, use one of the following Excel date functions:
=TODAY() -> inserts in a cell the current date. =NOW() -> inserts in a cell the current and current date.
Please remember that when using the Excel date functions:
- The date and the time returned will not be refreshed on an ongoing basis, but only when the chain is reopened or re-calculated.
- The functions take the current system clock date and time.
5. Auto-populate dates in Excel
To autofill a series of dates in which one day is incremented, you can use the Excel AutoFill function. It is a common way to automatically fill a column or row. To do so follow the below steps:
- Enter the original date in the first cell.
- Click the first date on your cell and then drag the fill handle to or from the cells you want Excel to add dates.
Autofill weekdays, months, or years:
There are two ways of automatically adding weekdays, months, or years to the selected range of cells. To so follow the below steps:
- You can use the above-mentioned Excel AutoFill options. Click the AutoFill Options icon and choose the option you want when the range is populated by sequential dates.
- Another way to enter your first date will be to right-click the fill handle and drag and release the fill handle through the cells you automatically want to fill with dates. Excel displays a context menu and selects the appropriate option.