While working with dates in Excel, we often have to insert a large number of dates as input. It is quite troublesome to sit down and insert so many dates manually. But with a few tricks in Excel, we can avoid this troublesome act and insert dates automatically. Today I will be showing how you can achieve that.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Simple Tricks to Insert Dates in Excel Automatically
Here we will see four easy and very useful tricks to insert dates in excel automatically. First, let’s see what is the basic way of inserting dates in excel.
1. The Basic Way to Insert and Format a Date in Excel
- Let us have a look at this dataset. We have a list of the Names of Candidates for an upcoming interview in Rennata Group. We have to insert dates for their interviews in the Interview Date column.
- Here is a short reminder of how you can insert and format a date manually. Follow the steps below.
- At first, select the cell and write a date in a conventional way, DD/MM/YYYY. This may vary depending on where you live.
- Then click Enter. You will find Excel automatically accepts it as a date. Here I select cell C5 and write a date, 20/05/2020.
- Now you can change the format of the date as you wish. Select the cell and go to the Home>Date option in the Number group of the Excel Toolbar.
- Click on the drop-down menu attached to it. You will get some options. Select the last option, More Number Formats.
- Then you will get a dialogue box called Format Cells. There you will find the Date option marked in the Category box. From the Type box, choose the format you want.
- Now we will learn some approaches to insert dates in Excel automatically.
2. Utilising the DATE Function to Insert Date in Excel Automatically
Excel, intuitively, provides us with a function called the DATE function. It takes three arguments, Year, Month, and Day. Then provides the date as output. For example, DATE(2020,12,23) = 23-12-20. In order to write down any date with this function rather than manually, follow the steps below.
- First, select the cell and insert the formula =DATE (Year, Month, Day).
- Then click Enter. You will find Excel turns it into a date automatically. Here, I select C5 and write the following formula.
- Then if you wish, you can change the format of the date by the method provided earlier.
3. Applying Excel Functions to Insert Date in Excel Automatically
Here we will see applications of two built-in functions to insert the current date in Excel automatically.
3.1 Using TODAY Function
You can write today’s date in any cell by Excel’s TODAY function. It does not take any argument. To do that follow the steps below.
- Just select the cell and write =TODAY(). You will find the cell filled with today’s date.
- I select cell C5 and write the following formula
<span style="font-size: 14pt;">=TODAY()</span>
- Now, click Enter key. You will see the following result.
- As today is October 12, 2022, the cell is filled with the date 12-10-22. Then if you wish, you can change the format of the date using the method mentioned earlier.
Note 1: You can use your keyboard shortcut to apply the TODAY function. Select the cell and press Ctrl + ;
Note 2: The TODAY function does not update automatically. That means, when one day will increase and the date will be June 23, the cell will contain June 22, not June 23. It will contain the date of when it was inserted until you change it.
3.2 Using NOW Function
The NOW function provides Today’s Date along with the Present Time as output. It also does not take any argument. To use this function, follow the steps below.
- Select the cell and write the following formula:
- Now click Enter key, and you will have the following result.
- By default, the output of the NOW function is in Custom format. But if you want to change the format of the cell, follow the steps provided earlier.
Note 1: You can use your keyboard shortcut to insert the NOW function. Press Ctrl + ;. Then press Space. Then press Ctrl + Shift + ;.
Note 2: Like the TODAY function, the NOW function does not update automatically.
4. Insert Multiple Dates Automatically in Excel
Let’s think the first date of the interview has been inserted manually or by the DATE function or in any other way. Now we want to insert dates for the rest of the candidates automatically.
There are 2 tricks you can use to do that.
4.1 Autofill Dates by Dragging the Fill Handle
Here, we will use the Fill Handle feature of excel. Follow the steps below.
- Move your mouse over the Bottom Right corner of the first cell and you will get a small Plus(+) sign. This is called Fill Handle.
- Drag it down the column, up to the cell you want to fill. And you will find all the cells filled with increasing dates one by one.
- Now if you want to fill the cells any other way rather than increasing them one by one, click on the small square box in the Bottom Right corner of the last cell. See the image below.
- And you will get these options.
- By default, you are in the Fill Series option. It fills the cells one by one with increasing dates.
- Now if you want to fill the cells with weekdays only, click on Fill Weekdays. The cells will be filled with upcoming weekdays (taking Saturday and Sunday as weekends, by default)
- If you want to insert dates by changing the months only, click on Fill Months. It will change the months one by one keeping the day fixed.
- You can even fill the cells by changing the years only, keeping the day and the month fixed. Click on Fill Years.
- Thus you can insert dates in the cells automatically by maintaining any criteria, by dragging the Fill Handle.
4.2 Autofill Dates by Fill Option from Excel Toolbar
Apart from using Fill Handle, we can also autofill dates from the Excel Toolbar. To do that follow the steps below.
- Write down the first date.
- Then select the cells where you want to insert dates automatically. Here I have inserted the date 20-05-20 into C5, then selected cells from C6 to C15.
- Then go to the Home>Fill option in Excel Toolbar under the Editing section.
- Click on the drop-down menu. And you will get these options. Click on Series.
- You will get a dialogue box named Series.
- You see, in the Series in section, the Columns option is selected. And in the Type section, the Date option is selected. Keep these unchanged.
- In the Date unit section, select the one which you want to increase.
- Select Day if you want to fill the cells with upcoming days.
- If you want to fill the cells with upcoming weekdays, select Weekday.
- Select Month if you want to fill the cells with increasing months keeping the day fixed.
- And if you want to fill the cells with increasing years keeping the day and month fixed, select Year.
- All these options were available in the Fill Handle, except one. In the Fill Handle, the Step value is set by default 1.
- If you want to set the step value to anything other than 1 in Fill Handle, you have to manually fill two or more cells with that step value. And then have to drag the Fill Handle. But here you can set the Step value according to your wish by just setting it into the Step value option. Set it and click OK. Here I select Weekday from the Date unit and keep the Step value as 3.
- You will find the selected column filled with increasing weekdays with step value 3.
So using these methods, you can easily insert dates in Excel automatically. This is quite helpful in our daily lives while we work in Excel. Do you know any other method? Let us know in the comment section.