In situation when you need to add or subtract a different number of months to/from a date based on one or more conditions, you can nest an EDATE formula inside of an IF statement. =EOMONTH(A3, B3) EDATE formula inside of Excel IF statement For example, with the start date in A3 and months in B3, the formula is: Knowing the start date and duration in months, calculating an expiry, due, anniversary or retirement date is as easy as pie.Īll you have to do is to supply the start date in the first argument of EDATE and the number of months in the second argument. The screenshot below shows the results of both formulas - the wrong ones are in the red font color: In this case, you can compute the last day of month by using the EOMONTH function. However, if the start day value is less than 31, EDATE will return a date exactly on the same day of the month in the past or future, as per its main purpose. For instance, the result of this formula is 2: It also maintains the last day of February in leap years. The EDATE function correctly returns the last day of month if the start day is 31.įor example, the formula below returns 3, which is a date 3 months after 3: =EDATE(A3, B3*12) EDATE formula to get last day of month The first form is more contact while the second variation is more convenient to use when you have a number of years in another cell. To move a date 10 years backward, the formulas are: The result is a date on the same day of the month, a specified number of months before or after the initial date.įor instance, to move a date 10 years forward from the date in A3, you can use one of these formulas: With the start date in a predefined cell, say C4, the formulas are: To get a date 5 months before 30 January 2025, the formulas go as follows: Apply the desired DATE format to the formula cell.įor example, to return a date 5 months after 30 January 2025, use one of these formulas:.To move the date backwards, supply a negative number - the result will be a past date. To shift the date forward, use a positive number - the output will be a future date.
a reference to the cell containing the start date.a string enclosed in quotation marks, e.g.For the start_date argument, supply a valid Excel date in one of these forms:.To make a basic EDATE formula in Excel, follow these steps: To display the result as a date, apply a Date format of your choosing. By default, the EDATE function returns a serial number representing a date internally in Excel.
0 kommentar(er)
