Today I will be showing how you can add dates automatically in Microsoft Excel.
Download Practice Workbook
How to Add Dates in Excel Automatically
Let us have a look at this data set. We have the interview schedule of a company named Benitez Group.
We have the names of the candidates in column B.
Now we have to insert dates in column C for their interviews.
1. Adding Single Date in Excel
First, you have to enter the first date of the column. You can insert this in a few ways.
1.1. Adding Single Date Manually
You can insert a date manually in Excel.
For this, you have to select the cell and write the date in any traditional format.
For example, 05/13/2020
Or May 13, 2020.
If Excel can recognize it as a date, it will automatically accept it as a date.
But whether it can recognize a date or not depends on the custom setting of Excel.
If one format does not work, try another one.
Here I select cell C4 and enter the date 5/13/2020.
- Generally, texts are aligned left and dates (in fact, all number formats) are aligned right in Excel by default.
- So, after pressing Enter, if you find your date has been aligned right automatically, consider Excel has recognized it as a date.
- And if you find not, try another format or just select the cell and press Ctrl + Shift + 3.
- Then Excel will surely recognize it as a date.
Now, after entering the date, you can change the format of the date according to your wish.
To do this, select the cell and go to the Home>Date option in the Excel Toolbar under the section Number.
Click the drop-down menu.
From the options available, select More Number Formats.
And you will get a dialogue box called Format Cells, like this.
Note: You can just select the cell and press Ctrl + 1 on your keyboard. You will get the same dialogue box.
Now, in the Category box, under the option Date, choose any format you like from the box Type.
Here I select the format 14-Mar-01.
Now, I find my date has been converted from 5/13/2020 to 13-Mar-20.
1.2 Adding Single Date by Using Excel’s DATE() Function
Excel provides a built-in function called DATE().
You can use it to insert any date in Excel.
=DATE(Year, Month, Day)
- It takes three arguments, the number of the year, the number of the month and the number of the day.
- And returns the date.
Here I again select cell C4 and enter the formula
See, Excel has accepted it as date, 13-May-20.
Now obviously if you want, you can change the format of the date in the way mentioned in section 1.1.
1.3 Adding Single Date by Using Excel’s TODAY() Function
Excel has another built-in function called TODAY().
- It takes no argument and returns today’s date as output.
If you want to enter today’s date in ant cell, use this function.
Here I again select cell C4 and enter the formula
See, we have got today’s date, 12-Jul-21.
Note: TODAY() function takes today’s date from your computer’s date and time settings. So, if your PC has a wrong date set, you will get the wrong date.
2. Adding Multiple Dates in Excel
Now we have inserted the first date of the interview schedule. Next we want to insert dates for the remaining candidates automatically.
You can execute this in two ways.
2.1 Adding Multiple Dates by Using the Fill Handle
You can insert the remaining days by using the Fill Handle.
First, select the first cell. Then drag the Fill Handle through the rest of the cells.
Then click on the Auto Fill Options.
You will get a handful of options like this.
If you want to fill the cells with dates increasing one by one, select Fill Series or Fill Days.
To fill the cells with increasing weekdays only (Considering Saturday and Sunday as weekends), select Fill Weekdays.
In order to fill the cells with increasing months one by one, but keeping the day fixed, select Fill Months.
And if you want to fill the cells with increasing year one by one, but keeping the month and the day fixed, select Fill Years.
Now suppose the CEO of Benitez Group wants to take the interviews after 3 days each.
For example, the first interview is on 13-May, then on 16-May, then on 19-May, and so on.
So he has to fill up the remaining cells with dates of increment 3 in each step.
How can he accomplish this?
To accomplish this, fill the first two cells manually as mentioned in section 1.
I have inserted 13-May-20 in cell C4.
And 16-May-20 in cell C5.
Now drag the Fill Handle through the rest of the cells.
You will find dates inserted in the remaining cells with increment 3 in each step.
- If you want to insert dates with increment anything other than 1, fill up the first two cells manually with the increment and then from Auto Fill Options, select Fill Series or Fill Days.
- To insert only weekdays with increment anything other than 1, fill up the two cells manually with the increment and select Fill Weekdays.
- In order to insert dates with increasing only month keeping the day fixed, with increment anything other than 1, fill up the two cells manually with the increment and select Fill Months.
- And in order to insert dates with increasing the only year keeping the month and the day fixed, with increment anything other than 1, fill up the two cells manually with the increment and select Fill Years.
2.2 Adding Multiple Dates by Using Excel Toolbar Options
You can auto-fill dates from Excel Toolbar options too.
First, select the first cell and the rest of the cells which you want to auto-fill.
Then go to the Home>Fill option in the Excel Toolbar under the Editing section.
Click on the drop-down menu. From the options available, select Series.
You will get a dialogue box called Series.
In the Type options, select Date.
Next in the Date unit option, select by which you want to fill the cells.
- To fill the cells with increasing days, select Day.
- In order to fill the cells with increasing weekdays, select Weekday.
- To fill the cells with increasing months keeping the day fixed, select Month.
- And to fill the cells with increasing years keeping the month and the day fixed, select Year.
Then in the Step value box, enter the increment you want.
So, if you want to fill up the cells with upcoming weekdays with 3 days increment, the dialogue box will look like this.
Then click OK.
And you will find dates inserted into your cells with weekdays with an increment of 3 days.
3. Adding or Subtracting Days to a Date
Now we have finished entering an interview date for each of the candidates.
But due to some unexpected reason, now the Chief of the company wants to add 2 days to to the interview dates of each candidate.
He can accomplish this in three ways.
3.1 By Using Excel Formula
This is the easiest method to add or subtract dates to any date in Excel.
I want to add 2 days with the cell C4.
So I select a new cell and enter this formula
See, we get the day after 2 days, 15-May-20.
Now drag the Fill Handle to copy the formula to the rest of the cells with increasing cell reference.
Note: We can subtract any days from the dates in a similar way.
3.1 By Using Excel’s Paste Special Menu
There is another way you can add days to a date.
For this, you need not create a new column. You can add the dates in the existing column.
First, select a cell and enter the value you want to add.
Then copy the cell by either selecting and pressing Ctrl + C.
Or Right-click on the cell and select Copy.
Then select the cells where you want the days to be added. I select the interview dates, cell C4 to C19.
Again right-click on your mouse. Choose Paste Special>Paste Special.
You get the Paste Special dialogue box.
From the Paste menu, select Values.
And from the operation menu, select Add.
Then click OK. You will get all the dates increased by 2 like this.
3.3 By Using a Macro (VBA Code)
You can add days to a date by using a Macro.
First, take a new module and insert this VBA Code.
Sub Add_Day_To_Range() 'Adds 1 to each cell in the selected range Dim myValue As Variant myValue = InputBox("Enter the Days to be Added: ") Dim c As Range For Each c In Selection.Cells c.Value = c.Value + myValue Next c End Sub
- This site helped us understand and develop the code.
- To learn how to write and save and VBA codes, read this post.
- From the workbook, select the range of the dates (C4 to C19 for me) and press Alt + F8 on your keyboard.
- You will get a dialogue box called Macro. Select the Macro Add_Day_to_Range and then click Run.
- You will get an Input Box. In the Enter the Days to be Added field, insert the number of days which you want to add. Here I insert 2.
- Now click OK. And you will find 2 days added to each of the interview dates.
So using these methods, you can insert single or multiple dates in Excel, and then add subtract days to those dates in Excel. Do you know any other method? Let us know in the comment section.