Excel can increment easily add days to a date but months, quarters and years require a slightly different calculation. Here’s a single function that does all three.
Let’s start with the formula that does all the work. It uses a SWITCH function to determine the factor to use to get the correct addition using the EDATE function. In the image below the formula in cell B4 is.
=EDATE($A4,B$2*SWITCH(B$3,"m",1,"q",3,"y",12))This have been copied across and down.
The value in cel B2 is multiplied by a factor to achieve the correct date increment. The SWITCH determines the factor based on the entry in cell B3.
LAMBDA function
To test the LAMBDA function with three inputs, we use the following formula in cell F4.
=LAMBDA(dte,typ,qty,EDATE(dte,qty*SWITCH(typ,"m",1,"q",3,"y",12)))($A4,B$3,B$2)
The range name definition for the custom function fnDATESCALC is.
The custom function in action is shown below.
For your readers with a version of Excel that supports dynamic arrays, this single formula will output the entire result table…
=EDATE(+A4:A10,B2:D2*SWITCH(B3:D3,”m”,1,”q”,3,”y”,12))
Note: The plus sign in front of EDATE’s first argument is required (although you could also use 0+ as well).
Thanks Rick
The good ‘ol plus sign to the rescue again.