Garreth Hanley:
Excel Tips, a podcast for accounting and finance professionals brought to you by CPA Australia.
Neale Blackwood:
Welcome to the Excel Tips Podcast. My name is Neale Blackwood, and in this episode we're going to look at creating a custom function in Excel to increment dates. Now, you may not know it, but there's actually seven different calculations to increment dates in Excel.
There's one for days, there's another for weeks, another for fortnights. Then there's one for months, quarters, half years and years. So they boil down to one technique for days and another technique for months. But within those, there are factors involved. So it's really easy to add a day to a date. You can just use the plus in the formula to add days to a date.
When you're working with weeks or fortnights, you have to use a factor to multiply so that you can get the right number of days being added to a date. For months, there's actually an EDATE function. So the EDATE function works with months and it's very easy to add a number of months to a specific date using the EDATE function. So the EDATE function has two arguments.
It has a start date, then the comma, and then it has how many months you want to add to the start date. I'm going to hijack the EDATE function and actually make it work for all dates, not just the month. And we're going to use a custom function to do that.
Now, for all the technical instructions, check out the companion video. We do a deep dive into creating the custom function on that companion video on the INTHEBLACK website, where you'll also find the article which has all of the detailed instructions on how to create the custom function and how to use it. So EDATE function only works for months. So what I've done in the custom function is to add a third optional argument.
So there's been a number of articles on custom functions on the INTHEBLACK website, so you can check them out, but none of those have had an optional argument. So the optional argument can be omitted and we only use the first two arguments.
In that case, this custom function is going to work just like a normal EDATE function, so it's going to work for months. So if you use the third argument, you basically define the period to use, whether that's a day, a D, week, W, fortnight, F, a month, M, quarter, Q, H for half year, and Y for year. So obviously you can omit the M if you want to do a month calculation because that's going to be the default.
To define an optional argument in a custom function, you need to use the LAMBDA function to create the custom function. And when you're defining your arguments at the start of the LAMBDA function, you put square brackets around the optional argument name.
In general, it's best practise to list all optional arguments last when you've got multiple arguments. And so it will be the third argument and it will be optional. And to do that, as I mentioned, just put the square brackets around it. That tells Excel that that third argument doesn't have to be entered. What we need within the custom function is a way to specify the factor that we need to apply for the various periods.
So W needs a 7, fortnight needs a 14, quarters need a 3, half years need a 6, and the Y needs a 12. So those factors we can create within our custom function using the SWITCH function. Now this is a new function and it's sort of allows you to create a lookup table within the function.
So this function needs to be self-contained, so we need this lookup table to be within the function, and the SWITCH gives you that opportunity. Again, check out the companion video to see exactly how it works. But one of the things we can do with the SWITCH is also to have a default if the period argument, the third argument is omitted. If that's the case, then the period argument will return zero.
And so the SWITCH function can have zero as its last match to then define, in this case a one for the factor to use for the month. So the month and the day both have the factor one because there's no real adjustment required for them, but all of the other dates require some sort of adjustment to get the right date when we're incrementing it.
So one function can handle all seven different date increments. I did share a few keyboard shortcuts, which might be worth using. So if you need to open up the formula bar to see the custom function, which is quite common because custom functions tend to be fairly long when you're testing them with the LAMBDA function, the keyboard shortcut to open up the formula bar is Control, Shift, and the letter U. this is a toggle.
So if you press it again, it'll shrink the formula bar back to the normal size. Within the formula bar, it's worthwhile putting line breaks between sections of the LAMBDA syntax. So when you're testing the custom function, you use the LAMBDA function to do the testing within a cell.
Once you've done that, then you copy the LAMBDA function to a range name and it's actually the range name that becomes the custom function. To put a line break in the formula bar, you hold the Alt key and press Enter, Alt, Enter. Line breaks don't affect the calculation at all, but they do split the formula up into separate lines, which when you're dealing with LAMBDAs makes them easier to understand.
When you're creating the custom function, you have to use the name dialogue and there's a comment section and that's where you can actually add a lot of documentation for your custom function.
And to put a line break in that comment section, you used Control, hold the Control key and press Enter. So custom functions can simplify lots of different calculations. In this case, it's simplified incrementing dates. And again, as I mentioned, check out the companion video where you can see all of the details, how to use it, how to create it, how to put the documentation in, all that good stuff. Hope you found this useful, and thanks for listening.
Jackie Blondell:
If you're enjoying this podcast, you should check out our in-depth business and finance show INTHEBLACK. Search for INTHEBLACK on your favourite podcast app today.
