Spreadsheet or API: where the calendar should actually live

What to take away

A spreadsheet and a service both compute dates correctly. Where the calendar should actually live depends on who changes it and how the answer must be reproduced. The spreadsheet side includes Google Sheets, Microsoft Excel, Airtable, Smartsheet and Notion. The service side includes Google Calendar API, Microsoft Graph, Nager.Date, Workalendar and Calendarific.

The choice also depends on how a date travels between them without shifting by a day.

This page runs no calculation and recommends no product. It explains what each kind of tool is for and the contract a date must meet to cross between them. It also gives a request shape carrying everything a working-day service needs, plus one test set both tools should pass identically.

What each tool is for

The middle rows are where most organizations sit. Five people computing the same kind of deadline in five workbooks will not produce five identical calendars for long.

Named tools make the split concrete. Google Sheets and Microsoft Excel keep the formula in the cell, so the working is visible. Airtable, Smartsheet and Notion store dates in a shared record, which helps when several people touch the same calendar.

Google Calendar API and Microsoft Graph tie dates to events and accounts. Nager.Date, Workalendar and Calendarific supply holiday data.

The contract a date has to satisfy

Both sides count from an origin, and the origins differ. A sheet counts days from a date near the end of 1899; a service that speaks in seconds usually counts from the start of 1970, which is Unix time.

Neither number means anything without its origin. That is the argument for sending text in ISO 8601 order instead of either.

In a spreadsheet a date is an integer counting days from an origin. A time is the fraction after it.

On the common 1900 system, 1 January 2027 is 46388 and 1 March 2027 is 46447. =B2-A2 works because subtracting two integers subtracts two dates. A cell showing a date and a cell showing 46447 hold the same value in different clothes.

In a service a date is text: 2027-03-01. A moment is text with a time and an offset: 2027-03-05T23:30:00-05:00. The two are different types, and a difference between two dates is an integer while a difference between two moments is a duration.

Crossing the boundary is where days go missing:

The rule that survives all of these: send date-only values as YYYY-MM-DD text, never as a moment and never as a serial, and convert to a serial only inside the spreadsheet with DATEVALUE or DATE.

A request that carries everything

Google Calendar API and Microsoft Graph both accept ISO 8601 values. Nager.Date and Calendarific return holiday dates as text. Workalendar computes them inside a Python process. A request that carries its assumptions lets any of them be swapped without changing the answer.

A working-day service that cannot be argued with takes all of its assumptions as inputs and returns them with the answer. Vendor-neutral shape:

request
  operation:        add | subtract | between | hours
  start:            2027-03-01
  count or end:     10                (or an end date, for between)
  weekend:          "0000011"         (Monday through Sunday, 1 = rest day)
  calendar:         { id: "court-x", version: "2026-09" }
  start_counts:     false
  end_counts:       true
  zone:             "Europe/Paris"    (for hour operations only)

response
  result:           2027-03-15
  skipped:          [ ...the dates that were passed over... ]
  applied:          { weekend, calendar, start_counts, end_counts }
  computed_at:      2026-09-10T08:12:44Z

The applied block is what makes the answer defensible. Six months later the question is usually what it was computed with, and a response that echoes its inputs answers that in one line. The skipped list is what lets a human check the arithmetic in seconds.

The spreadsheet equivalent of this shape is a row with the same columns and the formula in the last one. If the row does not carry the weekend string and the calendar version, it is a result with no working.

One calendar, both tools

The closure list is a table with three columns: calendar id, date, and a note naming the publisher and date. Keep exactly one such table.

The spreadsheet loads it as a named range. The service loads it from the same file or source. Give each revision a version, and put that version beside every computed date in both tools.

No calendar is supplied here. Which publisher's list governs a deadline, and why the answer is a body rather than a country, is under adding business days; hour inputs for hours are under working hours.

The same tests in both tools

Run these through the spreadsheet formula and the service, with the same weekend string and no closures except where stated. All dates are in March 2027. Any difference is a convention to reconcile, not a defect to fix. The service can be Google Calendar API, Microsoft Graph, Nager.Date, Workalendar or Calendarific.

The seventh row is the one that reconciles most disputes. A step of five working days and a set of six working days describe the same span, and a tool that reports the step as five while another reports the set as six has not disagreed about anything.

When to move from the sheet to the service

Move when more than one person computes the same kind of date, or the date must land in another system's record. Move when someone can challenge a date and get the working back, or the calendar changes more often than every workbook updates.

Until then the spreadsheet is the better tool. Its working is visible, and the tests above take a minute to run. Google Sheets and Microsoft Excel cover most single-team cases. Airtable, Smartsheet and Notion add shared records.

Forward and backward counts are different operations. Each fails in its own way. The backward count, where a wrong closure costs a day you thought you had, is under subtracting business days.

Both tools must agree on endpoint conventions. Those are under counting between two dates.

Common questions

Can I call a service from a spreadsheet cell?

Usually, and it is often the worst of both: a formula that depends on a network call, with the calendar in one place and the working in another. Google Sheets can call APIs through Apps Script; Excel can through Power Query or Office Scripts.

If you do it, have the cell store the applied block alongside the result, rather than the date alone.

Should the service or the client decide the convention?

The request should state it and the response should echo it. A service with a silent default is a service whose answers cannot be compared with anything else.

How do I version a closure list?

A date stamp on the file and a version column in the table. Every computed date carries the version it used. When the list changes, old dates keep their old version and are not recomputed unless someone decides to.

Is a moment ever the right thing to send for a date?

Only for hour operations, where the instant matters. For day operations, a moment is a date with an extra way to be wrong, as monthly working-day counts show when a month boundary sits at local midnight.