- Jan 2, 2025
Custom Function to Increment Dates
- Neale Blackwood
- Custom Functions, Dates
- 0 comments
Excel can 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.
Sign up to hear about free Excel training.
I won't share your email with anyone.
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 EDATE.
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 cell 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.
