The ExcelHelpDesk team received the following request for help….
…..”However, all the dates have reset to 4 years and a day earlier. I cannot email you the spreadsheet as it contains protected and confidential information, but if you could send me the if,then formula that will convert any cell with a date value to 4 years and a day later that would be extremely helpful.
To solve this problem we will use one of Excel’s built-in functions DateAdd. This function provides the ability to Add and Subtract values from a given date. In this request we need to Add 4 years and 1 day to the date in a particular cell. So lets say we start with a spreadsheet that has the following dates that need to have 4 years and 1 day added to them.
The DateAdd function is only available through the use of VBA. It cannot be called directly as function on a worksheet. To enable the DateAdd function to be called from our spreadsheet we will create our own User Defined Function that then calls DateAdd to perform the necessary date calculations.
Lets create the User Defined Function within your worksheet. Open your workbook that contains the dates that need to changed, then start the Visual Basic Editor (Alt + F11) and copy the following macro into the editor
Function DateAddYearsMonthsDays(dteCurrentDate As Date, _ dblYear As Double, _ dblMonth As Double, _ dblDay As Double) As Date 'This function will add any number of Years, Months or Days to a given Date DateAddYearsMonthsDays = dteCurrentDate If Not (IsNumeric(dblYear) Or IsNumeric(dblMonth) Or IsNumeric(dblDay)) Then Exit Function If dblYear <> 0 Then DateAddYearsMonthsDays = DateAdd("yyyy", dblYear, DateAddYearsMonthsDays) If dblMonth <> 0 Then DateAddYearsMonthsDays = DateAdd("m", dblMonth, DateAddYearsMonthsDays) If dblDay <> 0 Then DateAddYearsMonthsDays = DateAdd("d", dblDay, DateAddYearsMonthsDays) End Function
Now we need to call the User Defined Function from the worksheet that contains the dates that need to be changed. In our example we will place the following formula into cell C7
The references for Years, Months and Days are global and can be changed at anytime to provide for different values to Add or Subtract from the dates in Column A. Those reference values are specified in cells B2:B4.
After including the formula to cell C7 and pressing enter the following result will be automatically calculated.
Now to complete the calculation of the remaining dates, drag and drop the formula in cell C7 down to the remaining cells C8:C10. The dates will be automically re-calculated.
The DateAdd function returns a date after which a certain time/date interval has been added.
The syntax for the DateAdd function is: DateAdd ( internal, number, date )
Number: is the number of intervals that you wish to add or subtract
Date: is the date to which the interval should be added
Interval: is the time/date interval that you wish to add or subtract. It can be one of the following values
The DateAdd function provides built-in logic to allow for Leap Years and Month End dates throughout the year. As a result your calculations are much simpler. If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment.