Table Of Contents
What Is Excel Add Months To Date?
Excel Add Months to Date, or EDATE, is a built-in function that adds the specified number of months to the supplied date and returns the next or the previous specified month for the same day, w.r.t the positive or negative values entered, respectively.
Key Takeaways
- The Excel Add Months to Date returns the previous or next month’s date w.r.t the current entered date. The date will be the same, however, the month will differ.
- In Excel, the dates are stored in the form of serial numbers, which makes the dates calculations easier.
- The default start date is 1st January 1900, with a serial number of 1,
- and the date from then on gets increased by 1, i.e., 1st January 1900, 2nd January 1900,
- 3rd January 1900, and so on, are 1, 2, 3, 4, and so on. For example – 1st January 2001 is 36892.
- The function finds its use while creating a financial model. For a monthly or quarterly model, we add one month or 3 months to the start date, respectively.
#EDATE Function
The syntax of the EDATE function is, =EDATE(start_date, months)
Assume you have taken a TV on EMI for six months. In addition, EMI will be deducted on the 05th of every month. Therefore, you need to create an EMI chart with the same date every month. For example, the 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 five months.
- We must first open the EDATE function in the B2 cell.
- Then, the start_date is our above month, i.e., B2 cell month.
- The next thing is how many months we need to add, i.e., one month, so we must supply 1 as the argument.
- Yes, we have the next month’s change but not the date here. So, we will fill down the formula to the remaining cells to have all the month’s dates as the 5th.
Add Months To Date Using EDATE Function
We can Add Months To Date using the EDATE function for the following scenarios,
- Different Result in Case of February.
- Special Due Date for Leap Year.
- Get Previous Months with Negative Number.
- Other Ways to Add Months to Date in Excel.
- EDATE with Other Functions.
Examples
We will consider examples to Add Months to Date for the above-mentioned scenarios.
Example #1 - Different Results in Case of February
Assume you have taken the loan, and EMI due is on the 30th of every month. The first will be on 30th Oct 2018, and EMI is for six months. Let us apply the EDATE function to arrive at all the month’s due dates.
If you notice the above function, the first thing is when it ends the year 2018 in Dec. After that, it automatically jumps to the next year, i.e., 2019 (refer to C5 cell).
The second thing is in February 2019, which is a non-leap year. So, it has only 28 days. So, the formula has returned the due date as 28th Feb 2019.
Example #2 - Special Due Date for Leap Year
Now, we will take an example of a year that has a leap year. In case of a leap, the year formula will return on 29th Feb, not the 28th Feb.
Example #3 - Get Previous Months with a Negative Number
We have learned how to get next month’s date from the current date. What if we need to get the previous months from the current date?
Assume we have the starting date as 05th Sep 2018, and we need to go back for six months.
We must apply the EDATE function, but in the no. of months to add mention -1 as the argument, as shown below.
Example #4 - Other Ways to Add Months to Date in Excel
Let us consider an alternative method to take months to days.
Here, the DATE function in excel extracts the year, month, and day from the above. So, the only thing we have done here is adding +1 to the month for all the cells.
Example #5 - EDATE with Other Functions
We can use the EDATE with other functions as well. For example, assume we want to count the number of invoices generated from a certain date to a certain date.
We have the below formula to count invoices from Count No. of Invoices from 17th Nov 2018 to 16th Dec 2018.
Important Things To Note
- In the number of months, we can add any number. If the date falls next year, it will automatically change the year.
- If leap year comes, Feb last date will be the 29th. If not, it will be the 28th.
- A positive number will give future months, and a negative number will give previous months.
- If the date format is not there for the starting date, we will get the #VALUE! error.
Frequently Asked Questions (FAQs)
Where is the EDATE function in Excel?
Give a brief idea of the EDATE function to Add Months to Date.
Why is the Excel Add Months to Date, EDATE function not working?
