Kition
sheetFormulas & Functions

Invoice Due-Date Tracker

Due dates that skip weekends and office holidays with WORKDAY, business days with NETWORKDAYS, and a status for every invoice.

Invoice Due-Date Tracker — Sheet sample rendered by Kition

Each invoice is due its payment terms after issue, moved to the next working day when that lands on a weekend or on one of the office holidays in the named range `Holidays`: `WORKDAY(issued+terms-1,1,Holidays)`. TEXT names the weekday, NETWORKDAYS counts the working days the client had, and IFS turns the paid date and the statement date into Paid, Overdue or Open. Due dates that moved are shown in italics. Below, SUMIFS, AVERAGEIFS and MINIFS summarise what is still owed and what falls due next.

Steps

57 commands
showcase.yaml87 lines
steps:
  - { command: range.setValues, params: { range: A1, values: [["Receivables Calendar"], ["Small design studio · invoices issued January to March 2027"], [Statement date, "2027-03-31"]] } }
  - command: range.setValues
    params:
      range: M4
      values:
        - [Office holidays]
        - ["2027-01-01"]
        - ["2027-01-18"]
        - ["2027-02-15"]
        - ["2027-04-02"]
        - ["2027-05-31"]
  - { command: formulas.defineName, params: { name: Holidays, refersTo: "=Sheet1!$M$5:$M$9" } }
  - command: range.setValues
    params:
      range: A5
      values:
        - [Invoice, Client, Amount, Issued, Terms, Due, Day, Work days, Paid on, Days late, Status]
        - [INV-1041, Alder & Pine Studio, 1850, "2027-01-08", 30, "=WORKDAY(D6+E6-1,1,Holidays)", "=TEXT(F6,\"ddd\")", "=NETWORKDAYS(D6,F6,Holidays)", "2027-02-05", "=IF(I6=\"\",MAX(0,$B$3-F6),I6-F6)", "=IFS(I6<>\"\",\"Paid\",$B$3>F6,\"Overdue\",TRUE,\"Open\")"]
        - [INV-1042, Brightwater Clinic, 4200, "2027-01-15", 45, null, null, null, "2027-03-05"]
        - [INV-1043, Copperleaf Bakery, 960, "2027-01-29", 30, null, null, null, "2027-03-08"]
        - [INV-1044, Driftwood Books, 2375, "2027-02-05", 30]
        - [INV-1045, Elmstead Dental, 6400, "2027-02-12", 60]
        - [INV-1046, Foxglove Florists, 1180, "2027-02-26", 30, null, null, null, "2027-03-26"]
        - [INV-1047, Granite Peak Gym, 720, "2027-03-05", 15]
        - [INV-1048, Harbor Light Cafe, 3150, "2027-03-19", 30]
  - { command: edit.fillDown, params: { range: "F6:H13" } }
  - { command: edit.fillDown, params: { range: "J6:K13" } }
  - command: range.setValues
    params:
      range: A15
      values:
        - [Outstanding, "=SUMIFS(C6:C13,K6:K13,\"<>Paid\")"]
        - [Overdue, "=SUMIFS(C6:C13,K6:K13,\"Overdue\")"]
        - [Average days late (paid), "=AVERAGEIFS(J6:J13,K6:K13,\"Paid\")"]
        - [Next due date, "=MINIFS(F6:F13,K6:K13,\"Open\")"]
        - [Work days until then, "=NETWORKDAYS(B3,B18,Holidays)-1"]
  - { command: home.fontSize, params: { range: A1, size: 20 } }
  - { command: home.bold, params: { range: A1 } }
  - { command: home.fontColor, params: { range: A1, color: "#3730a3" } }
  - { command: home.fontColor, params: { range: A2, color: "#6b7280" } }
  - { command: home.bold, params: { range: "A3:B3" } }
  - { command: home.numberFormat, params: { range: "B3", format: "d mmmm yyyy" } }
  - { command: home.alignLeft, params: { range: "B3" } }
  - { command: home.fontColor, params: { range: "B3", color: "#1d4ed8" } }
  - { command: home.fillColor, params: { range: "A5:K5", color: "#3730a3" } }
  - { command: home.fontColor, params: { range: "A5:K5", color: "#ffffff" } }
  - { command: home.bold, params: { range: "A5:K5" } }
  - { command: home.alignCenter, params: { range: "D5:K5" } }
  - { command: home.alignRight, params: { range: "C5" } }
  - { command: home.numberFormat, params: { range: "C6:C13", format: "#,##0" } }
  - { command: home.numberFormat, params: { range: "D6:D13", format: "d mmm" } }
  - { command: home.numberFormat, params: { range: "F6:F13", format: "d mmm" } }
  - { command: home.numberFormat, params: { range: "I6:I13", format: "d mmm" } }
  - { command: home.numberFormat, params: { range: "J6:J13", format: "+0;-0;0" } }
  - { command: home.alignCenter, params: { range: "D6:K13" } }
  - { command: home.numberFormat, params: { range: "E6:E13", format: "\"Net \"0" } }
  - { command: home.bold, params: { range: "F6:F13" } }
  - { command: home.borders, params: { range: "A6:K13", preset: insideHorizontal, color: "#e5e7eb" } }
  - { command: home.borders, params: { range: "A13:K13", preset: bottom, color: "#3730a3" } }
  - { command: home.conditionalFormat, params: { range: "F6:G13", rule: { type: expression, formula: "=$F6<>$D6+$E6", style: { font: { italic: true, color: { Rgb: 10105874 } } } } } }
  - { command: home.conditionalFormat, params: { range: "K6:K13", rule: { type: expression, formula: "=K6=\"Overdue\"", preset: lightRedFillDarkRedText } } }
  - { command: home.conditionalFormat, params: { range: "K6:K13", rule: { type: expression, formula: "=K6=\"Paid\"", preset: greenFillDarkGreenText } } }
  - { command: home.conditionalFormat, params: { range: "K6:K13", rule: { type: expression, formula: "=K6=\"Open\"", preset: yellowFillDarkYellowText } } }
  - { command: home.bold, params: { range: "A15:A19" } }
  - { command: home.numberFormat, params: { range: "B15:B16", format: "$$#,##0" } }
  - { command: home.numberFormat, params: { range: "B17", format: "0.00 \"days\"" } }
  - { command: home.numberFormat, params: { range: "B18", format: "ddd d mmm yyyy" } }
  - { command: home.alignRight, params: { range: "B15:B19" } }
  - { command: home.fillColor, params: { range: "A15:B19", color: "#eef2ff" } }
  - { command: home.borders, params: { range: "A15:B19", preset: outside, color: "#a5b4fc" } }
  - { command: home.bold, params: { range: "M4" } }
  - { command: home.fontColor, params: { range: "M4", color: "#3730a3" } }
  - { command: home.numberFormat, params: { range: "M5:M9", format: "ddd d mmm" } }
  - { command: home.fillColor, params: { range: "M5:M9", color: "#f3f4f6" } }
  - { command: home.columnWidth, params: { cols: "A:A", width: 136 } }
  - { command: home.columnWidth, params: { cols: "B:B", width: 124 } }
  - { command: home.columnWidth, params: { cols: "C:C", width: 60 } }
  - { command: home.columnWidth, params: { cols: "D:G", width: 50 } }
  - { command: home.columnWidth, params: { cols: "H:H", width: 64 } }
  - { command: home.columnWidth, params: { cols: "I:J", width: 60 } }
  - { command: home.columnWidth, params: { cols: "K:K", width: 70 } }
  - { command: home.columnWidth, params: { cols: "L:L", width: 24 } }
  - { command: home.columnWidth, params: { cols: "M:M", width: 100 } }
  - { command: sheet.read, params: { range: "F6:K13" }, as: schedule }
  - { command: sheet.read, params: { range: "B15:B19" }, as: summary }
  - { command: formulas.nameManager, params: {}, as: names }

Checks

5 checks on every build
  • results.schedule.values = [[46426,"Mon",21,46423,-3,"Paid"],[46447,"Mon",30,46451,4,"Paid"],[46447,"Mon",21,46454,7,"Paid"],[46454,"Mon",21,null,23,"Overdue"],[46490,"Tue",41,null,0,"…
  • results.summary.values = [[12645],[3095],[1.25],[46490],[8]]
  • inspect.used = "A1:M19"
  • Diagnostics: none, the kit's own check finds nothing wrong
  • The saved document opens again, and its thumbnail matches thumb.png within the render tolerance

Related

More Sheet samples
sheetFormulas & Functions

Dynamic Array Event Report

Three spilling summaries of a conference sign-up list, built with UNIQUE, SORT, FILTER, SORTBY, CHOOSECOLS and TAKE.

1 sheet; used range A1:I23
sheetFormulas & Functions

Sales Commission Calculator

Quarterly payouts for eight reps with tiered marginal commission from a named LAMBDA, VLOOKUP rate bands and IFS bonuses.

1 sheet; used range A1:M13
sheetFormulas & Functions

XLOOKUP Order Form

An order form that fills in item names, categories, prices and volume discounts from a catalog with XLOOKUP and INDEX/MATCH.

1 sheet; used range A1:L21
sheetFormulas & Functions

Exam Score Statistics

A class's test scores described with AVERAGE, MEDIAN, MODE, STDEV, QUARTILE and PERCENTILE, binned with FREQUENCY into a histogram.

1 sheet; used range A1:L28