Add Months to Date in Excel
In excel we all how to insert serial numbers, how to insert a series of dates. We know every time it increments the previous number by one. However, when we work with dates and incrementing only the month is quite a difficult task because when you drag the dates using fill down it will always increment the date by one, not the month.
For an example take look at the below example where I already have date 01-01-2019 and 02-01-2019 in the first two cells.
Now using fill down handle I will drag these numbers and look what happens.
Only day is incremented but month remains the same until it crosses the last day of the month i.e. 31st Jan 2019. Once it crosses the 31st day it jumps to the next.
Now lest add +1 to the existing date and see what happens?
Even this formula incremented the date by one, not the month. Then how do we increment the month like we have incremented the date by one? In this article, we will show you the ways of adding months to dates in excel.
How to Add Months to Date in Excel by Using EDATE Function?
In excel we have a built-in function called EDATE which adds the specified number of months to the supplied date returns the next specified month for the same day. Before I show you how to use EDATE in excel, let me introduce you to the syntax of EDATE function.
- Start Date: This is the existing data you are trying to add the months to the date. This should be in date format, if the date is supplied as the text we will get an error value.
- Months: This is simply the number of months we need to add to the Start Date. If the number is positive it will give you the month greater than the start date and if the number is negative it will give the month less than the start date.
Example #1 – Using the EDATE with Alternate Function
Assume you have taken a TV on EMI for 6 months. EMI will be deducted on the 05th of every month. Now you need to create an EMI chart with the same date for every month. First EMI is on 05-02-2019.
In the next five rows we need 05th Mar 2019, 05th Apr 2019 and so on for the next 5 months.
Step 1: Open the EDATE function in the B2 cell.
Step 2: Start date is our above month i.e. B2 cell month.
Step 3: Next thing is how many months we need to add i.e. 1 month so supply 1 as the argument.
Step 4: Yes we have got the next month change but not the date here. Fill down the formula to the remaining cells to have all the months date as 5th.
Alternative: We can use the same formula by making the starting date as absolute reference supply the number of months to add as the serial number reference.
Step 1: Enter the EDATE formula as usual and select the date and make it an absolute reference.
Step 2: Now the number of months to add argument give reference to the serial number instead of entering it manually. Don’t make this as an absolute reference keep this as a relative reference.
Step 3: This will also give the exact result as the previous technique.
Example #2 – Different Result in Case of February
Assume you had taken the loan and EMI due is on 30th of every month. First will be on 30th Oct 2018 and EMI is for 6 months. Let’s apply the EDATE function to arrive at all the months due date.
If you notice the above function the first thing is the moment it ends the year 2018 in Dec it has automatically jumped to the next year i.e. 2019 (refer C5 cell).
The second thing is in February 2019 which is a non-leap year, has only 28 days. So the formula has returned the due date as 28th Feb 2019.
Example #3 – Special Due Date for Leap Year
Now take an example of the year which has a leap year in it. In case of a leap, year formula will return 29th Feb, not the 28th Feb.
Example #4 – Get Previous Months with Negative Number
We have learned how to get the next month date from the current date. What if we need to get the previous months from the current date?
Assume we have starting date as 05th Sep 2018 and we need to go back for 6 months.
Apply EDATE function but in the no., of months to add mention -1 as the argument.
Example #5 – Other Ways to Add Months to Date in Excel
We can months to days by using other methods as well. This is a bit complicated method but just to have a knowledge I am showing this to you.
Here DATE function extracts the year, month, and day from the above but only thing we have done here we are adding +1 to the month for all the cells.
Example #6 – EDATE with Other Functions
We can use EDATE with other functions as well. Assume you want to count the number of invoices generated from a certain date to a certain date, we need to use EDATE function.
I have below formula to count invoices from Count No., of Invoices from 17th Nov 2018 to 16th Dec 2018.
Things to Remember While Adding Months to Date in Excel
- In the number of months, you can add any number. If the date is falling in next year it will automatically change the year as well.
- If leap year comes Feb last date will be 29th, if not it will be 28th.
- A positive number will give future months and negative numbers will give previous months.
- If the date format is not there for starting date then we will get the #VALUE error.
This has been a guide to Add Months to Date in Excel. Here we discuss how to Add Months to Date in Excel using by EDATE Formula along with practical examples and downloadable excel template. You may learn more about excel from the following articles –