Excel tips: Excel date calculations — simplified

Excel’s date calculations involving days or months require different techniques. 

In this article, you’ll learn how a custom function can simplify date calculations, how to include an optional argument just like a built-in function, and how the SWITCH function lets you create a look up table within a formula. 

Custom functions

Excel enables custom functions using the LAMBDA function. Custom functions are easily shared between files. 

Previous articles have covered creating custom functions without macros and for loan schedules.

Custom functions can have optional arguments just like Excel’s built-in functions. This article demonstrates how to include an optional argument in a custom function. 

Dates 

The EDATE function handles month-based calculations but doesn’t handle day-based calculations. A custom function could combine both calculation methods into a single, flexible function.

The EDATE function has two arguments, the start date and the number of months to add. A custom function could have an optional third argument to specify the type of period to use. 

If the third argument is omitted, the custom function would work with months like the EDATE function. 

Custom EDATE function

This custom function will be named fnEDATE. I recommend using a naming convention for all your custom functions that differentiates them from Excel’s built-in functions — I use the fn prefix. When entering the formula, typing fn will then display all the custom functions.

The first argument for the custom function is the start date. The second argument is the quantity. These match the EDATE function. The optional third argument is the period. It is best practice to list optional arguments last.

The period defaults to months if omitted. The period argument will accept a single text character code to select the period — see Table 1.

If the code is entered manually into the formula, it must be enclosed in quotation marks.

The LAMBDA function to test the custom function is shown in Figure 1. The formula is in cell D2 and has been copied down the column to test with different inputs.

Custom functions must be tested using a special LAMBDA syntax. This syntax has a separate set of brackets on the end of the LAMBDA formula that contain the three inputs to test the formula. They are highlighted in yellow in Figure 1.

The first line of the formula lists the three custom function arguments: start, quantity and period. The square brackets around the period argument define it as optional. This means Excel will accept the custom function with only two arguments. 

The three cells in the second set of brackets (highlighted) at the end of the formula are used for testing purposes and are supplied to the three arguments in the first line of the formula. 

The value in cell A2 becomes the start argument. The value in cell B2 becomes the quantity argument. The value in cell C2 becomes the period argument.

The second line of the formula determines if the period argument has a day-based code (d, w or f). The OR function returns TRUE if the period equals any of the three listed codes.

The third line handles the day-based calculation. This is in the TRUE section of the IF function. Days can be added to a date using the plus sign. In this case, the quantity value needs to be adjusted based on the period chosen. The quantity must be multiplied by a factor (1, 7 or 14) to correctly handle the selected period. 

The SWITCH function simplifies the factor calculation. Instead of using multiple IF functions, we can determine the factor with a single SWITCH function. 

The SWITCH function creates a self-contained lookup table inside your formula. This is useful when choosing between multiple options. 

SWITCH(period,"d",1,"w",7,"f",14)

This SWITCH function reviews the period value. If the period value is d then the SWITCH function returns 1, w returns 7 and f returns 14. The result of this SWITCH function adjusts the quantity used to increment the start date. 

The month-based calculations are in the last two lines of the formula which is the FALSE section of the IF function.

The EDATE function uses the start date. The quantity is used in the second argument of the EDATE function and must be adjusted by a factor based on the period selected. Again, the SWITCH function is used. 

SWITCH(period,"m",1,"q",3,"h",6,"y",12,0,1)

This SWITCH function must handle a missing period argument. If the period argument is omitted, then the period argument returns 0. The 0,1 entries at the end of the second SWITCH function defaults the calculation multiple to 1 (months) if the period argument is omitted.

Once the LAMBDA has been tested it can be converted into a custom function using a range name. 

Select and copy the LAMBDA function without the separate set of brackets on the end. See Figure 2 for the section of the formula that needs to be copied.

Press Ctrl + Alt + F3 function key to open the New Name dialog.

Click in the Refers to box and paste. Enter the function name fnEDATE and click OK — see Figure 3. The custom function is ready to use throughout the file.

The custom function should also be tested — see Figure 4. The function is in cell E2 and has been copied down.

The custom function should also be tested by omitting the third argument — see Figure 5. Cell E5 omits the third argument. It returns the same result as the LAMBDA version in cell D5 using m as the period.

Excels shows the argument names when you start to create the formula just like a built-in function. See Figure 6.

If you copy a sheet with a custom function to another file, the custom function is also copied to the new file. 

Extra documentation can be added to the custom function using the Comment section of the Edit Name dialog — see Figure 7. 

Now when the function is used, more information is displayed. See Figure 8.

Custom functions enable the creation of flexible formulas that can improve Excel’s existing functionality.

The companion video and Excel file go into more detail to demonstrate these techniques.