DATE YEAR A2-5 MONTH A2 DAY A2 Above formula will give you a date exact 5 years before. If month is less than 1 zero or negative value Excel subtracts the magnitude of that number of months plus 1 from the first month in the specified year. In the example shown the formulas used in column D are.
DATE YEAR A11MONTH A1DAY A1 Bob wrote with a similar problem.
Syntax is YEARFRAC start_date end_date basis it returns the number of days between 2 dates as a year. Then click to select a cell that you want to calculate a future date based on. Last year with the same month of today and the same date of today 1. Ensure the starting date is properly formatted go to Format Cells press Ctrl 1 and make sure the number is set to Date.