Hey, How About a Date?

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:

1. no inputs, default current month, 5 weeks, starting Sunday, text dates

2. year and month as offsets; if year>1899 they are taken as literal year and month

3. set number of weeks; set Monday start

4. set year; specify formats

5. numeric dates, requires formatting

6. 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

d.holirules

This sets up an array of "rules," or parameter sets used to calculate holidays. The rules table can be output to the sheet for review but must be maintained in the module. Note that form 1 and form 2 share the last two columns of the table, since they each require two parameters besides the month. The green comment rows explain the meaning of the parameters and their position in the table.

d.notwe and d.nthwd

The rules fall under two forms, "not weekend," which offsets a fixed date that falls on a weekend to a weekday, and "nth weekday," which returns the given day of the input month. These functions are derived by brute logic, as explained in the link above. There is no reason to output them in the sheet except for troubleshooting.

Output Functions: d.holidays and d.workdays

There are two output functions that call on the internal functions: d.holidays returns a list or grid of holidays, and d.workdays uses d.holidays along with a weekend index to return working days. These can be used in team scheduling sheets.

d.holidays

This retrieves and names each parameter from d.holirules to be fed to d.notwe and d.nthwd, which calculate the actual dates. Note that the shared columns 5 and 6 are each given two names so that the two internal functions can be given properly named arguments. The dates are presented in either a grid of columns by year or a single column, and with names or not.

Here are four cases to illustrate the options. The output ranges have been formatted.

1. no inputs, default current year list

2. list for any given year

3. list for multiple years in one column

4. list for multiple years in a grid with names

d.workdays

Workdays are determined by subtracting weekends and holidays from the calendar. WORKDAY.INTL is used to allow for weekend input, limited here to the seven possible two-consecutive-day weekends. The default weekend is 1=Saturday/Sunday, the most common alternative is 7=Friday/Saturday used in some countries. Function d.workdays calls d.holidays, passing the weekend indicator along for the "not weekend" rule.

Below are side-by-side holidays and workdays, in two weekend cases, to show that holidays are absent from workdays. Of course, in case 2, if the weekend is different the holiday rules would also be different; this is just to show that the output adjusts to the weekend input. There is one Friday holiday in case 2, only because it is defined as a Thursday plus one.

1. weekend = blank, default weekend Sat / Sun

2. weekend = 7, alternate weekend Fri / Sat

Other Functions

d.quarter and d.quarterend: return the quarter, text or number, and quarter end date

d.quarter returns quarter as text representing the first letters of the months, or as number 1 to 4. d.quarterend calls d.quarter with the number option and returns the end date.

Here holidays are used as a convenient date spread to show the two quarter output options and the quarter end date.

d.weekdays: return a list of selected weekdays only

Returns selected weekdays, entered as a string of concatenated weekday numbers.

d.thisweekday: return the selected weekday of the current week

Returns the selected weekday of the current week.

d.leapyear: return Boolean is leap year or not

Returns Boolean leapyear, based on rules explained in timeanddate.com. Note that Excel incorrectly interprets 1900 as a leap year, admitted and explained by Microsoft. See implications of that below in the extended date functions.

For years >1899, we can also count the days in the year; if 366 then it's a leap year. However, by applying the leap year rules we can correct the Excel error in 1900, and it allows us to backward-project to years before 1900, whether they would have been leap years, if the same rules had been in use then.

d.xdatedif: return difference of any two AD dates in various units

Improved. Returns the difference between any two A.D. dates in one of five units.

1. from > 1899, to > 1899, type 3=weeks, decimals=2

2. from > 1899, to = blank (today), type=5, y/m/d

3. from > 1899, to < 1900, type 2=months

4. reverse of 3, nolabel=1, output is numeric

5. from > 1899, type=blank, default years

d.xdatecalc: return any AD date as offset from input date

Improved. Returns the calculated date in three eras: year < 0 as"#BCE"; year 0000-1899 as text; year > 1899 as date, with text option.

In these examples, output is mixed numbers and text; numeric dates are format mm/dd/yyyy and are right-aligned; text dates are format mmm d, yyyy and are left-aligned. The five output rows are a sequence of the increment inputs.

1. input > 1899, negative increment: output > 1899 is number; < 1900 is text

2. same

3. input < 1900, negative increment: output < 0 is flagged #BCE

4. input < 1900, positive increment: output < 1900 is text; > 1899 is number

5. same as 4, with text-only output

The table below illustrates the effect of Excel's error in interpreting 1900 as a leap year. It shows the beginning of Excel date counting, date numbers 1 to 63, with several weeks hidden. Date number 60 is February 29, 1900, which did not actually exist. The first 59 dates are interpreted as incorrect weekdays: January 1 was actually Monday, not Sunday. Weekdays catch up as of March 1, from which Excel is correct, other than dates being one additional day from January 1 than they should be. As Microsoft makes the case (see d.leapyear above), this discrepancy is so obscure that the cost to fix it far outweighs the benefit.

Now, d.xdatecalc uses an offset of 4000 years, which based on leap year rules, results in leap year calibration, therefore omitting the incorrect leap day. However, Excel still interprets the weekdays incorrectly, as seen where 12/31/1899 (text) and 01/01/1900 (number) are both Sunday. However, when the text-only option is used the dates and weekdays are correct.

d.periodprofile: return an array of hybrid buckets

This returns a combination of periods specified in days, weeks, months, quarters and years. Defaults are defined, but periods can be omitted by entering 0. Dates represent the end of each bucket.

1. with no arguments, defaults defined

2. custom profile

3. only months and years

free code!

1. Download and open the text file (e.g. with Notepad)

2. Select all and copy (Ctrl+A, Ctrl+C)

3. Open AFE, create module d

4. Paste the code (Ctrl+V)

5. Save (Ctrl+S) to synch to the sheet

Date LAMBDAs.txt

Download TXT • 10KB

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.