Say you are working on a project and want to adjust the project schedule. But you want to add 6 months to the project’s completion date for an overview.
Or maybe, you are just casually trying to find out what day is 6 months from today. Whatever your need may be, you may come across the question of how to add 6 months to a date.
This guide will help you find the answer to this question once and for all. In this guide, you will see how to add 6 months to a date in your Excel. We will also show you what not to do when you are trying to add 6 months to a date. And finally, as a bonus, you will also see how to subtract 6 months as well.
So, let’s jump right in!
What Not to Do
If you are new to Excel probably this is the first thing that you are going to try. This is the simplest method but also the most impractical if you want to add 6 months to your desired date. This is only practical if you are adding days to your desired date.
Say, you have a date in cell A3, and you want to add 6 months to it.
Click on another cell, and in the formula bar, enter:
As you can see in the picture, the cell returns a date.
But this Date is not accurate. See, this formula assumes that there are 30 days to a month and multiplies by 6 to get the number of days in 6 months. This formula then adds this to your desired date.
As Excel stores date as serials, this works to add days to your desired date. But this is impractical because not every month has 30 days. So, you will almost always get a wrong answer with this formula. But if you are looking to add only days to your date, you can use this formula.
How to Add 6 Months To a Date in Excel
So, To properly add 6 months to your desired date, you can use the EDATE method or the DATE method. We have explained the process step-by-step in this guide. We also provided images with each of the step so you can easily follow along.
What are we waiting for then? Let’s start.
With the EDATE Function
The EDATE function is one of the quickest ways to add months to a date. It is also the most common way to add a number of months to your required date.
EDATE requires two arguments. A start date and the number of months you want to add or subtract. To use this Function to add months to a date just follow this guide:
First, you need to enter the following formula in the formula bar:
In the parenthesis, put in your desired date that you want to add 6 months to. In our case, we typed the date of writing this guide which is 8/2/23.
Add a comma and then add the number of months you want to add. For this demonstration, we put in 6. This will add 6 months to the date we put in before the comma.
Make sure to put in quotation marks before and after the date to ensure that you get the correct date you want to receive. Otherwise, you will get an incorrect date as shown in the picture.
But, what if you put in the formula and you receive a number? Don’t fret. It’s probably that Excel is just showing the date as a serial.
Excel normally stores dates as serial numbers starting from January 1, 1900. And if your Excel is not configured right, whatever dates you may type in, will show as serial numbers. You can see this in our example here.
To fix this, on your home tab under the Numbers menu, click on ‘General’.
In the drop-down menu that appears, click on Short Date and you’re done. You now have 6 months added to your date.
You can also take better control of this ask by pressing CTRL+1 to bring up the Format Cells dialogue. And just select Dates under the Number tab. This will give you a lot more options and date formats to use.
The interesting thing with the EDATE function is you can use this with a cell reference too.
Say you have the same date written in a cell. To add 6 months to the date, just put the cell reference in the parentheses instead of the actual date.
You can also do this without writing the number of months you want to add.
Say, you have another column where you have written how many months to add to your existing date. Instead of writing the number of months to add to your date, just select the cell reference. This will produce the same result. Here is our example.
As you can see, the formula works the same way. If you are using a cell reference you don’t even have to put in the pair of inverted commas too.
Adding 6 Months to A Range of Dates
Now, let’s assume that you have a range of dates in a row and you want to add 6 months to each of the dates and show it in a subsequent row. You can also do this with the EDATE function.
To do this, similar to what we have shown you above, in a subsequent cell type in the EDATE formula with the first cell reference as an argument.
As you can see, we used the B3 cell for reference. Then just hover on the bottom right corner of the B3 cell to see the Autofill Handle (which looks like a solid plus sign)
Then just click and drag down to copy the formula to the cells below. And Excel will automatically add 6 months to the subsequent cells.
You can see that Excel has added 6 months to all of the dates from column A in the subsequent cells.
With the DATE Function
The DATE function is a very versatile function and can help you calculate dates by adding or subtracting days, months, or even years from a date. Unlike the EDATE function which can only take months for the addition or subtraction, the DATE function takes days, months, and years.
So, for our purpose of adding 6 months to a date, you can follow this process:
Click on a subsequent empty cell, and in the formula bar type in
=DATE(YEAR(), MONTH(), DAY())
In our case, we only want to add 6 months to the current date. So in the parenthesis after month, add the date or cell reference of the date you want to add 6 months to. In our case it was A3.
Add a plus sign and type in 6 to add 6 months to the date.
As you can see, the DATE function has added 6 months to the date stored in the A3 cell.
If you want to do this for the rest of the dates as well, similar to the EDATE method click on the bottom right corner to invoke the Autofill Handler and click and drag.
As the image shows, the formula is copied to the subsequent cells and 6 months are added to your dates.
In this formula, the MONTH argument returns the value of the month stored in the A3 cell and then adds 6 to it. Then the DATE function converts the values to a date which is 6 months in the future.
You can also add any number of years, months, and days to your existing date with this DATE function similarly.
In this guide, we showed you how to add 6 months to your formula. As a bonus, you can also subtract 6 months or any number of months from your desired date. To do this, just put a negative sign before 6 for all of the methods we discussed before. And et voila, you will have 6 months subtracted from your desired date.
For example, if you want to subtract 6 months with the EDATE function just use the formula
-EDATE(cell reference, -6)
In our case, we used the A3 cell again. So, our formula looks like this
As you can see in the picture, the EDATE function has returned a date that is 6 months in the past. Now, you can just use the Autofill Handler to copy the formula to the cells below.
To Wrap Up
Adding months to a date or a series of dates is one of the most common things you will do while working in Excel on a project. Although, if you are new to Excel you would be tempted to just put a plus sign after a date and add the number of days to that date, you surely won’t get an accurate result.
To add 6 months to your excel dates, the EDATE, and the DATE functions are the most efficient and quick ways. In this step-by-step guide, we tried to show you extensively how each of these functions works and how you can add a number of months to your date.
With the DATE function, you can even add a number of days or years to your desired date. You can also subtract months using these two methods that we shared with you. So? What are you waiting for? Practice these two methods and see exactly when your project’s going to end.