What Is Google Sheets Add Months To Date?
Add Months to Date in Google Sheets is a method to display specified number of months to the given date and retrieve the next or the previous specified month for the same day, w.r.t the positive or negative values entered, respectively. We can use the EDATE function in Google Sheets for the same.
In simple terms, the Google Sheets Add Months To Date returns the previous or next month’s date as per the number of months entered w.r.t the current entered date. The date will be the same, however, the month will differ. For example, we have the monthly EMI data given below to be paid each month and the first EMI date, i.e., the start date. Let us Add Months To Date Using EDATE function.
Select cell B3, enter the formula =EDATE(B2,1) and drag the formula from cell B3 to B7 using the fill handle, as shown below.
We get the output as shown above. The dates remain the same and the months have changed to every next month from January till June.
[Special Note: We can display the date in any of the valid formats. In this article, we have used the long date format in most of the examples. Since, we are adding months this format is easy to comprehend.]
Table of Contents
Key Takeaways
- The Add Months To Date In Google Sheets enables one to add the specific number of earlier or later months to a given date value. The result is a date indicating the same day in the determined month.
- In Google Sheets, users can add months to a date value to determine specific dates, such as periodic loan repayment due dates, i.e., the EMI dates.
- We can use the EDATE(), DATE(), EOMONTH(), etc, functions to add months to a date in Google Sheets. The retrieval depends on the month number argument, i.e., if it is a positive number, then, it will retrieve future dates or if it is a negative number, then, it will retrieve earlier dates.
Add Months To Date Using EDATE Function
The syntax of the EDATE formula in Google Sheets is,
The arguments of the EDATE formula in Google Sheets are,
- start_date: A date value in the valid format. It is a mandatory argument. We can enter the date using the DATE() function, to avoid supplying the date value in an invalid format.
- months: It is an optional argument which is an integer indicating the count of months before or after the start_date. While a positive month’svalue results in a future date and a negative value gives a past date. Also, if the supplied value is not an integer, the value is truncated and the whole number is considered.
We can use the EDATE function In Google Sheets in 2 ways, namely,
- Access from the Google Sheets ribbon.
- Enter in the worksheet manually.
Method #1 – Access from the Google Sheets Ribbon 🡢
Choose an empty cell for the output 🡢 select the “Insert” tab 🡢 click the “Function” option right arrow 🡢 click the “Date” option right arrow 🡢 select the “EDATE” function, as shown below.
The “EDATE” formulaappears, as shown below. Enter the argument as the cell reference.
Method #2 – Enter in the Worksheet Manually 🡢
- Select an empty cell for the output.
- Type =EDATE( in the cell. [Alternatively, type =E or =ED and double-click the EDATE function from the Google Sheets suggestions.]
- Enter the arguments as cell values or cell references and close the brackets.
- Press Enter to view the outcome.
Examples
We will consider some examples to Add Months to Dates in Google Sheets using the EDATE(), DATE(), EOMONTH() functions in Google Sheets to use it effectively.
Example #1 – Using the DATE Function
The table below shows the date values in column A and we will add months to the dates using the DATE function.
The procedure to add months using the DATE() function is,
First, select cell B2, enter the formula =DATE(Year(A2),Month(A2)+1,Day(A2)) and press “Enter”. We get the output shown below, i.e., the same date same year but a different month, because in the formula we have added +1 for the Month() function.
Now, drag the formula from cell B2 to B7 using the fill handle to get the other results.
Since we added +1 to the MONTH() function the months have increased by one month. Similarly, we can change the value with positive and negative numbers to get earlier or later months.
Example #2 – Using the EOMONTH Function
The EOMONTH function in Google Sheets retrieves the preset code of the date i.e., the serial number of the last day of the month w.r.t the given start date.
The data given below consists of the start dates, the month argument and the details of the respective month arguments of what will be retrieved. We sill add months using the EOMONTH() function.
The steps to add months using the EOMONTH() function is,
Step 1: Select cell D2, enter the formula =EOMONTH(A2,B2) and press “Enter”. We get the output shown below, but in the number format that is the serial number of the date in Google Sheets.
Step 2: Now, drag the formula from cell D2 to D7 using the fill handle. We get the complete results, but with serial numbers, as shown below.
Step 3: As we are unsure if the dates retrieved are right,we will convert the serial numbers as follows:
- Select column A, right-click and select the “Copy” option.
- Select column D, right-click, select the “Paste Special” option right arrow and click the “Format Only” option.
Then, we will get the final output, as shown below.
Example #3 – Different Result In Case Of February
The below data shows two lists in columns A and C. We will fill column A with dates from each month in 2023 and column C with dates from each month in 2024, based on the first dates in each column.
The steps to add months for an usual year(2023) and the leap year(2024) using the EDATE() are,
Step 1: Select cell A3, enter the formula =EDATE(A2,1)and press “Enter”.
Step 2: Drag the formula from cell A3 to A13 using the fill handle.
Step 3: Select cell C3, enter the formula =EDATE(C2,1) and press Enter.
Step 4: Drag the formula from cell C3 to C13 using the fill handle.
The EDATE() accepts the previous cell date value as the first argument and 1 as the second argument since we require the date in each consecutive month.
We supply the last day of January 2023 and 2024 to the EDATE() in cells A3 and C3. So, the function output will be the last day of the next month, February, of the corresponding year. However, as 2023 is a non-leap year, and 2024 is a leap year, February has 28 and 29 days in the respective years.
Thus, the formula for Google Sheets add months to date returns different outputs in the case of February based on whether the year in the given date is a leap or non-leap year.
And hence, the subsequent dates or the EDATE() output in the remaining target cells get altered from 31st, and are the 28th and the 29th in columns A and C, respectively, as shown in the above image.
Important Things To Note
- When using the EDATE() to perform Google Sheets add months to date, supply the date value to the EDATE function using the DATE() to ensure it is valid and in a valid date format. Otherwise, the function will return the #VALUE! error.
- If the EDATE()’s second argument is not an integer, then, the value will get truncated.
Frequently Asked Questions (FAQs)
We often forget in which category a function falls, here, the “EDATE” function. Then, we can insert the function as follows:
Choose an empty cell 🡢 select the “Insert” tab 🡢 click the “Function” option right arrow 🡢 click the “All” option right arrow 🡢 select the “EDATE” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
Alternatively, we can find the Functions icon to insert the EDATE function by following the path shown below.
• Choose an empty cell 🡢 click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
• A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
• Here, click the “Functions” option 🡢 click the “All” option right arrow 🡢 select the “EDATE” function, as shown below.
A few reasons the Add Months to Date in Google Sheets may not work are,
• The 2nd argument in the EDATE function is incorrectly provided.
• If the date entered is not in valid format, then, we will get an error. Therefore, we can use the DATE() function for the input date values as well.
Download Template
This article must help understand Add Months To Date In Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to Add Months To Date In Google Sheets. Here we add month to date to find specific date using DATE, EDATE, EOMONTH functions with examples & work template. You can learn more from the following articles. –
Leave a Reply