Today I present my 12, scratch that, now 14 new and improved custom date functions. To be clear, these are not new Excel functions like the ones I recently presented, they're mine. To maintain the distinction at the point of use, my LAMBDAs are prefixed with a module name, usually single letter, and all my module and function names are lower case. I use module d for date functions.
Date functions are easy targets. There are many ways we use dates that are not so easy with the native function set. Want a quick calendar? a list of next year's holidays or workdays? of Mondays and Thursdays? These range from simple to complex developments, but are all simple to use, and that's the point. Plus, they are effective at demonstrating techniques and use of arrays.
A few quick points:
I use few required arguments, often none, and lots of optional ones. This lets me define a super-easy default while offering maximum flexibility.
The arguments are presented to the user as whole words or as intuitive as possible and explained in the description.
Internal LET names are used liberally to default, transform and alias inputs in stages and keep the logic easy to follow.
Note the comment formatting:
comments above the function show in the sheet: /** comment */
comments within the function are internal: /* comment */
Calendar Function
d.calendar
This has 8 arguments, all optional. If nothing is entered it returns a calendar array of the current month with 7 columns, Sunday to Saturday, and 6 rows, 5 weeks plus the headings. Come on, that's almost easier than looking in your system tray. Then there are many ways to customize, including single-column output.
The default output is text, good for lookup but can't feed formulas. The option to output date values requires sheet formatting, but enables them to be used in formulas. The option to output dates in a single column enables its use as an input array.
Here are six cases that show the options:
no inputs, default current month, 5 weeks, starting Sunday, text dates
year and month as offsets; if year>1899 they are taken as literal year and month
set number of weeks; set Monday start
set year; specify formats
numeric dates, requires formatting
numeric date column, after formatting
Holidays and Workdays
Holidays are complex to calculate, so to simplify the output functions I use internal functions to do the heavy lifting. Three internal functions are used by two output functions. The internal functions d.holirules, d.notwe and d.nthwd can return output for review but are not intended for sheet use. Their descriptions clearly indicate that they are for internal use. One output function, d.holidays, calls the three internal functions. The other output function, d.workdays, calls d.holidays.
Holiday calculation rules were developed for the Calendar sheet of the Startup file, explained here and available here.
Internal Functions: d.holirules, d.notwe and d.nthwd
Output Functions: d.holidays and d.workdays
Other Functions
d.quarter and d.quarterend: return the quarter, text or number, and quarter end date
d.weekdays: return a list of selected weekdays only
d.thisweekday: return the selected weekday of the current week
d.leapyear: return Boolean is leap year or not
d.xdatedif: return difference of any two AD dates in various units
d.xdatecalc: return any AD date as offset from input date
d.periodprofile: return an array of hybrid buckets
free code!
Some Points About Weekday and Weekend Parameters
It takes considerable effort to understand Excel's many weekday and weekend parameters. The confusion began long ago with the decision to call the first day of the week Sunday, even with a standard weekEND of Saturday and Sunday. It makes more sense to put the weekend at the end of the week rows, rather than splitting it across rows. International standards have adopted this view but are up against legacy practices.
Certain Excel date functions allow entry of weekend or weekday parameters but were modified from early versions which presumed Sat/Sun weekend. Many countries observe Fri/Sat weekend, and companies may also define their own work week patterns.
First, distinguish between weekend and weekday definition. Weekend separates working days from non-working days; weekday defines the numbering of the seven days in the week. They are independent of each other, although I would argue that they should be linked. As with calendars, it makes sense to put the weekend at the end. For Sat/Sun weekend, the counting should start from Monday; for Fri/Sat weekend it should start from Sunday. Crossover of these numbering schemes leads to confusion. My suggestion to Microsoft is to enable one-time settings, if not defer to OS settings.
NETWORKDAYS.INTL and WORKDAY.INTL use the same weekend parameter list:
1 - 7 for two-day weekends, from Sat/Sun (omitted or 1) to Fri/Sat (7)
11 - 17 for one-day weekends, from Sun (11) to Sat (17)
custom binary string of seven 0's (work) and 1's (off) starting Monday:
"0000011" is equivalent to 1, that is, Sat/Sun off
"0000110" is equivalent to 7, that is, Fri/Sat off
any custom pattern, such as Tue-Thu work week = "1000111"
WEEKDAY uses a similar set for the return_type parameter to mean something different
1 = 1 Sun to 7 Sat
2 = 1 Mon to 7 Sun
3 = 0 Mon to 6 Sun
11 - 17 for 1 Mon to 7 Sun (11, same as 2) to 1 Sun to 7 Sat (17, same as 1)
WEEKNUM return_type also has ten, yup 10, parameters to define the start of week. For week numbers I will always use ISOWEEKNUM, the international standard version, which has zero, that's 0 parameters.
Bottom line, when building custom functions, I will only accommodate weekends 1 and 7, which covers users in most countries and companies. Weekday numbers are more for internal calculations, generally I will stick with type 1 or 2.
Comments