Schedules that are hard to write with Excel’s WORKDAY and EOMONTH — do not let an expired holiday table stay silent

Translated from the canonical Japanese page recipes/excel-workday.md.

A page for people who compute business days in Excel: accounting, general affairs and IT departments that build tables of closing dates and payment dates with WORKDAY, EOMONTH and NETWORKDAYS. You do not need to read the language specification.

Kairos does not replace Excel. It splits the roles: producing the list of dates goes to Kairos; totals and layout stay in Excel. Kairos can write its results as tab-separated values (TSV), so they enter Excel as a table (Handing it to Excel).

At a glance

Requirement	Excel	Kairos
Last business day of the month	`WORKDAY` and `EOMONTH`	`within(month)` and `last`
Payment on the 25th, the business day before if it is a day off	`WORKDAY` and `DATE`	`nth(25)` and `roll`
The 10th business day	`WORKDAY` and `EOMONTH`	`nth(10)`
Three business days before quarter-end	`WORKDAY` and `EOMONTH`	`within(quarter)` and `shift`
Every 3 business days	needs a helper column	`stride(3)`
First and third Wednesday	two `WEEKDAY` formulas	the union of `nth(1)` and `nth(3)`

The Kairos expressions are written under the standard @JP premise (a business-day stream bizDay derived from weekends and Japan’s 2026 holidays). The form that brings in your own holiday table is in When the holiday table runs out. The # eval: and #=> lines are verification comments for this site; you may delete them.

Last business day of the month

In Excel, read it as “one business day before the 1st of the next month”. A2 is any day in the month and 祝日 is the name of the holiday-table range:

=WORKDAY(EOMONTH(A2,0)+1,-1,祝日)

In Kairos: “cut the business-day stream by month, take the last point”. It is written in the order it is read:

# eval: 2026-01-01..2027-01-01
@JP
bizDay |> within(month) |> last
#=> 2026-01-30 2026-02-27 2026-03-31 2026-04-30 2026-05-29 2026-06-30
#=> 2026-07-31 2026-08-31 2026-09-30 2026-10-30 2026-11-30 2026-12-31

The end of January (1/31 Sat) and the end of May (5/30 Sat, 5/31 Sun) retreat to Friday. The Excel formula gives one month per cell; a table for the year means copying it into 12 rows. Give Kairos the range and the 12 points come out at once.

Payment on the 25th — the business day before if it is a day off

In Excel: “one business day before the 26th”. If the 25th is a business day it stays the 25th; on a weekend or holiday it becomes the business day before:

=WORKDAY(DATE(YEAR(A2),MONTH(A2),26),-1,祝日)

In Kairos: “make the 25th of every month, and if it is not a business day, move it back”:

# eval: 2026-01-01..2027-01-01
@JP
everyDay |> within(month) |> nth(25) |> roll(Preceding, on: bizDay)
#=> 2026-01-23 2026-02-25 2026-03-25 2026-04-24 2026-05-25 2026-06-25
#=> 2026-07-24 2026-08-25 2026-09-25 2026-10-23 2026-11-25 2026-12-25
#~> 範囲外 2026-01-01..2026-01-02（holidays2026 covering 2026-01-01..2026-12-31, asof 2026-01-05）

January 25 (Sun) moves to the 23rd (Fri), April 25 (Sat) to the 24th, October 25 (Sun) to the 23rd.

The last line is a one-line note from the language: “if a point landed on January 1, the business day before it would be outside the holiday table (in 2025)” (the interval 2026-01-01..2026-01-02 excludes its right end, so it is January 1 only).

No point of this expression lands there, but the expression does not stay silent about an interval that could depend on what lies outside the table. To move forward to the next business day instead, use roll(Following, on: bizDay).

The 10th business day

In Excel: “10 business days after the end of the previous month”:

=WORKDAY(EOMONTH(A2,-1),10,祝日)

In Kairos: “cut the business-day stream by month, take the 10th”:

# eval: 2026-01-01..2027-01-01
@JP
bizDay |> within(month) |> nth(10)
#=> 2026-01-16 2026-02-16 2026-03-13 2026-04-14 2026-05-19 2026-06-12
#=> 2026-07-14 2026-08-17 2026-09-14 2026-10-15 2026-11-16 2026-12-14

January leaves Coming of Age Day (1/12) out of the count and May leaves out the Golden Week holidays, giving 1/16 and 5/19.

Three business days before quarter-end

In Excel: “four business days before the 1st of the next quarter”. A2 is the first day of the quarter. -1 is the last business day, -4 is three business days before it:

=WORKDAY(EOMONTH(A2,2)+1,-4,祝日)

Get the count wrong by one and the date is off by a day, and Excel says nothing. In Kairos: “from the last business day of the quarter, step back 3 on the business-day scale”:

# eval: 2026-01-01..2027-01-01
@JP
bizDay |> within(quarter) |> last |> shift(-3, unit: bizDay)
#=> 2026-03-26 2026-06-25 2026-09-25 2026-12-28
#~> 範囲外 2026-12-29..2027-01-01（holidays2026 covering 2026-01-01..2026-12-31, asof 2026-01-05）

December: three business days back from 12/31 is 12/28. The last line is the note “the 2027 table is missing”, carried along by the arithmetic: stepping back three business days moves that unknown region onto December 29–31. Any point due in that interval would come from outside the table, so it cannot be known. The point 12/28 itself is correct.

The difference between roll (move onto) and shift (count) is in spec §4.4.

Every 3 business days, and the first and third Wednesday

In Excel, “every 3 business days” becomes a helper column: each row is 3 business days after the row above, copied downwards (=WORKDAY(B2,3,祝日)). Change the starting point and the whole table is rebuilt. Kairos takes the starting point as an argument:

# eval: 2026-01-01..2026-03-01
@JP
bizDay |> stride(3, from: 2026-01-05)
#=> 2026-01-05 2026-01-08 2026-01-14 2026-01-19 2026-01-22 2026-01-27
#=> 2026-01-30 2026-02-04 2026-02-09 2026-02-13 2026-02-18 2026-02-24 2026-02-27

“First and third Wednesday” takes two WEEKDAY formulas in Excel (the first Wednesday, and 14 days after it). In Kairos, take “the 1st in each month” and “the 3rd” from the stream of Wednesdays and join them with |. Measured results are in Writing “twice a month” as one expression (Japanese).

When the holiday table runs out

This is the main point. The third argument of WORKDAY is the holiday-table range, and even when the table stops at 2026, computing a 2027 date silently returns an answer with zero holidays. January 1, 2027 becomes a business day. You notice after a payment has slipped by a day.

In Kairos, the table carries covering: (the range in which the table is valid). Computing outside that range still yields points, but an annotation saying “computed outside the table” (範囲外, out of coverage) always comes along. Here, with your own holiday table, computing October 2026 to March 2027:

# eval: 2026-10-01..2027-04-01
premise JP {
  calendar-system: Gregorian
  tz: "Asia/Tokyo"
  wkst: Mon
}

@JP
holidays2026 = [2026-01-01, 2026-01-12, 2026-02-11, 2026-02-23, 2026-03-20,
                2026-04-29, 2026-05-03, 2026-05-04, 2026-05-05, 2026-05-06,
                2026-07-20, 2026-08-11, 2026-09-21, 2026-09-22, 2026-09-23,
                2026-10-12, 2026-11-03, 2026-11-23] covering: 2026..2026
satSun = everyDay |> filter(d => weekday(d) == Sat or weekday(d) == Sun)
bizDay = everyDay \ (satSun | holidays2026)

bizDay |> within(month) |> last
#=> 2026-10-30 2026-11-30 2026-12-31 2027-01-29 2027-02-26 2027-03-31
#~> 範囲外 2027-01-01..2027-04-01（holidays2026 covering 2026-01-01..2026-12-31）

The three 2027 points are computed with “zero holidays”, just as in Excel, but the same output states the interval that is “outside the table”. next (the next N points) prints the same annotation. The “remaining runway” in the coverage summary is the number of days the table has left, and it is shown on every evaluation. That is the signal that it is time to extend the table.

Extend the holiday table once a year (the Cabinet Office announces Japan’s national holidays every February). Add the rows and extend covering: by one year; that is all. The three ways of writing covering: are in table literal; the background of “holiday tables rot silently” is in the blog post (Japanese).

Handing it to Excel (TSV)

--tsv (from 1.0.10) writes the results as tab-separated values, one point per line. Annotations and the coverage summary go to standard error, so they do not get mixed into the file:

kairos list --tsv --from 2026-01-01 --to 2027-01-01 month-end.kairos > month-end.tsv

expr	source	uid	date	time	epoch_ms	note
bizDay |> within(month) |> last	kairos-3d28b4e324d5-series@kairos-lang.org	2026-01-30		1769698800000	
bizDay |> within(month) |> last	kairos-3d28b4e324d5-series@kairos-lang.org	2026-02-27		1772118000000	
bizDay |> within(month) |> last	kairos-3d28b4e324d5-series@kairos-lang.org	2026-03-31		1774882800000	

Excel: do not double-click the file. Use “Data → From Text/CSV”, pick the file, choose UTF-8 as the file origin and Tab as the delimiter. date comes in as a date and epoch_ms as a number. time is filled only for definitions that carry a time of day. note is the annotation text when the point falls in an interval “outside the table”; empty means the computation stayed inside the table.

PowerShell: Import-Csv month-end.tsv -Delimiter "`t" -Encoding utf8 | Format-Table date, note.
VBA: call kairos through Shell, have it write the TSV, and read it line by line.
The meaning of the columns and when to use --json instead: reference implementation README (Japanese).

Installing on Windows and running it every day from Task Scheduler: Kairos for Windows administrators.

Try it in your browser

Run the self-contained form (premise included, holiday table inside the expression) in the Playground — the 12 last business days of 2026 come out. Swap in any of the other three expressions in the comments (payment on the 25th, the 10th business day, three business days before quarter-end) and try them. Everything runs in the browser; nothing is sent anywhere.

Month-end in depth: Last day of the month (comparison with cron, loading the same expression onto US holidays, patterns for running it automatically)
Measured results for the same family: the two meanings of “the 31st of every month”, the Nth business day, every N business days — study 11, measured section

Vocabulary and where the specification is:

Vocabulary: within · nth · last · roll · shift · stride
Bringing in holiday data and its freshness (covering:, asof:): spec §4.10 · nonWorking and bizDay