How to Add Dates in Excel Automatically

Adding Dates by Fill Option from Excel Toolbar

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.

A data set in Excel

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.

Like MM/DD/YYYY

For example, 05/13/2020

Or 13-May-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.

Adding Single Date in Excel

Note: 

  • 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.

Number Format Options in Excel

Click the drop-down menu.

From the options available, select More Number Formats.

Number Format Options Available in Excel

And you will get a dialogue box called Format Cells, like this.

Format Cells Dialogue Box in Excel

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.

Format Cells Dialogue Box with Options Selected in Excel

Now, I find my date has been converted from 5/13/2020 to 13-Mar-20.

Dates Converted to Expected Format in Excel

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.

Syntax

=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.

For example, DATE(2020,5,13)=13-May-2020.

Here I again select cell C4 and enter the formula

=DATE(2020,5,13)

DATE Function in Excel

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().

Syntax

=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

=TODAY()

TODAY Function in Excel

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.

Dragging the Fill Handle in Excel

You will get a handful of options like this.

Auto Fill Options in Excel

If you want to fill the cells with dates increasing one by one, select Fill Series or Fill Days.

Fill Days Auto Fill Option in Excel

To fill the cells with increasing weekdays only (Considering Saturday and Sunday as weekends), select Fill Weekdays.

Fill Weekdays Auto Fill Option in Excel

In order to fill the cells with increasing months one by one, but keeping the day fixed, select Fill Months.

Fill Months Auto Fill Option in Excel

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.

Fill Years Auto Fill Option in Excel

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.

Two Dates Inserted in Excel

Now drag the Fill Handle through the rest of the cells.

Dragging Fill Handle in Excel

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.

Fill Option in Excel

Click on the drop-down menu. From the options available, select Series.

Fill Options in Excel

You will get a dialogue box called Series.

Series Dialogue Box in Excel

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.

Series Dialogue Box with Options Checked in Excel

Then click OK.

And you will find dates inserted into your cells with weekdays with an increment of 3 days.

Adding Dates by Fill Option from Excel Toolbar

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

=C4+2

A Formula to add days with date in Excel

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.

Copying formula by dragging the Fill Handle in Excel

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.

A Value Inserted in a Cell in Excel

Then copy the cell by either selecting and pressing Ctrl + C.

Or Right-click on the cell and select Copy.

Copying a Cell in Excel

Then select the cells where you want the days to be added. I select the interview dates, cell C4 to C19.

A Range of Cells Selected in Excel

Again right-click on your mouse. Choose Paste Special>Paste Special.

Paste Options in Excel

You get the Paste Special dialogue box.

From the Paste menu, select Values.

And from the operation menu, select Add.

Paste Special Dialogue Box in Excel

Then click OK. You will get all the dates increased by 2 like this.

Dates Increased by 2 in Excel

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.

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.

Macro Dialogue Box in Excel

  • 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.

Input Box in Macro

  • Now click OK. And you will find 2 days added to each of the interview dates.

2 Days Added with Dates Using Macro in Excel

Conclusion

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.

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo