Two time intervals overlap when each starts before the other ends. That test includes full containment. Here’s how to apply it in Numbers on your Mac to compare publication windows, validate dates and handle touching endpoints.

This workflow is for someone planning publication windows in a spreadsheet. All dates and labels below are synthetic. You’ll compare two windows per row and get a clear result without writing a program.

What counts as an overlap?

First, decide whether an interval includes its ending instant. This article uses a start-inclusive, end-exclusive interval, written [start, end). A window from 09:00 to 12:00 includes its start and stops at noon. Another window beginning at 12:00 therefore does not overlap it. This matches the event convention in iCalendar: DTSTART is inclusive and DTEND is non-inclusive. RFC 5545, section 3.6.1.

For two valid, positive-length intervals, the complete overlap test is:

A.start < B.end AND B.start < A.end

The reasoning is straightforward. Two windows are separate if A ends before or exactly when B starts, or B ends before or exactly when A starts. If neither separation condition holds, they share time. This derivation also explains why containment requires no additional overlap condition.

Consider A running from 09:00 to 12:00. If B runs from 08:00 to 13:00, neither endpoint of B falls inside A, yet B contains all of A. A test that only asks whether B starts or ends inside A would miss this case. The two-comparison rule catches it.

Keep this synthetic test set beside your spreadsheet. Every time below belongs to November 10, 2026, and uses the same time basis.

A window B window Expected result Reason
09:00–12:00 11:00–13:00 Overlap Partial intersection
09:00–12:00 10:00–11:00 Overlap A contains B
09:00–12:00 08:00–13:00 Overlap B contains A
09:00–12:00 09:00–12:00 Overlap Identical windows
09:00–12:00 12:00–13:00 No overlap Endpoints only touch
09:00–12:00 13:00–14:00 No overlap Gap between windows

Set up comparable dates and times in Numbers

Use full dates and times for timed publication windows. A clock time alone cannot distinguish this morning from tomorrow morning. Numbers also supplies missing components: entering only a time adds today’s date. Hiding a date or time changes its display, while the stored component still participates in calculations. Format dates, currency and more in Numbers on Mac.

Create one table with these columns, using row 1 for the headings:

Column Heading Purpose
A Pair A neutral label for the comparison
B A start First window’s beginning
C A end First window’s exclusive end
D B start Second window’s beginning
E B end Second window’s exclusive end
F Input check Whether comparison is possible
G Overlap result The answer

Select the input cells in columns B through E. In the Format sidebar, choose Cell, then set Data Format to Date & Time. Choose displays that show both the date and time. The Smart Cell View at the bottom of the window lets you inspect both components of a selected value. Apple’s date and time formatting instructions.

For a reproducible first example, put Sample pair in A2 and enter the following formulas in the indicated cells. These construct A as 09:00–12:00 and B as 10:00–11:00 on November 10, 2026. Treat every endpoint in this example as UTC; record that convention beside the table.

Cell Formula
B2 =DATE(2026,11,10)+DURATION(0,0,9)
C2 =DATE(2026,11,10)+DURATION(0,0,12)
D2 =DATE(2026,11,10)+DURATION(0,0,10)
E2 =DATE(2026,11,10)+DURATION(0,0,11)

DATE builds a date from year, month and day, avoiding ambiguous date strings. DURATION accepts weeks, days and hours as its first three arguments, so DURATION(0,0,9) supplies nine hours. These are spreadsheet formulas, not Terminal commands. DATE, DURATION.

To enter a formula, select its destination cell, type =, enter the expression and press Return. Numbers supports comparisons between cell values and displays their Boolean results. If your settings use a comma as the decimal separator, use semicolons between function arguments instead of commas. Calculate values using data in table cells in Numbers on Mac, Functions overview.

Validate the windows before testing overlap

For this task, require four recognized date/time values and an end strictly later than its corresponding start. A reversed interval needs correction. A zero-length publication window also needs a separate decision, because it represents no occupied time under the chosen convention.

Enter this formula in F2:

=IF(AND(ISDATE(B2),ISDATE(C2),ISDATE(D2),ISDATE(E2)),IF(AND(B2<C2,D2<E2),"Ready","Invalid window"),"Check dates")

The outer test checks whether every endpoint is a date value. The inner test checks the ordering of both windows. ISDATE identifies date values, AND requires all its conditions to be true, and IF selects the corresponding result. ISDATE, AND, IF.

The sample should return Ready. If it returns Check dates, inspect the input cells rather than treating the row as separate windows. If it returns Invalid window, check for equal endpoints, reversed entries or an omitted next-day date. Leave any formula error visible until you have corrected its cause.

Detect overlap, including full containment

Enter this formula in G2:

=IF(F2="Ready",IF(AND(B2<E2,D2<C2),"Overlap","No overlap"),"Check inputs")

For a valid row, the two comparisons implement the overlap rule directly: B2 is A’s start, E2 is B’s end, D2 is B’s start and C2 is A’s end. AND returns true only when both comparisons succeed. Apple’s AND documentation.

The sample returns Overlap, because B sits entirely inside A. Change D2’s hour to 8 and E2’s hour to 13; it should still return Overlap, now with B containing A. Change them to 12 and 13; the result should become No overlap.

Use the earlier test table to check the remaining cases. For additional comparisons, enter another pair on the next row and copy the F and G formulas into that row. Inspect the copied formulas to confirm that their references point to the new row.

This layout compares the two windows supplied on each row. It does not automatically compare every window in a separate event list with every other event. For a small list, create one row for each distinct pair you need to examine, avoiding duplicate reversed pairs.

Identify which window contains the other

Overlap and containment answer different questions. If you also need to know whether A fully contains B, add an H column named A contains B and enter:

=IF(F2="Ready",AND(B2<=D2,E2<=C2),"Check inputs")

For B contains A, add an I column and enter:

=IF(F2="Ready",AND(D2<=B2,C2<=E2),"Check inputs")

These containment tests permit shared boundaries. Identical windows therefore return TRUE in both columns. If your task requires containment with space on both sides, replace both non-strict comparisons in the relevant containment formula with strict comparisons. That is an additional editorial condition; it does not change the overlap result.

Keep civil dates, instants, offsets and zones separate

Choose the meaning of your input before applying the formulas. A civil date such as 2026-11-10 names a calendar day without specifying a time zone. It is suitable for an all-day publication label. TC39’s Temporal.PlainDate API explicitly models this kind of date independently of a zone. Temporal.PlainDate.

An instant identifies a point on the timeline. An offset such as +01:00 describes the difference from UTC at that time. A named zone such as Europe/Paris supplies regional time rules. These are different pieces of information: a fixed offset does not carry a named zone’s changing rules. Temporal.ZonedDateTime.

For example, the synthetic timestamps 2026-11-10T10:00:00+01:00 and 2026-11-10T09:00:00Z identify the same instant. Comparing only their displayed hours would give the wrong ordering.

The spreadsheet formulas above compare the values you supply; they contain no named-zone conversion step. For timed windows originating in different zones, first resolve each endpoint to an instant and express every endpoint in UTC. Keep the original zone information separately if you need to reconstruct the intended local schedule.

For an automated conversion, the documented API path is to construct a Temporal.ZonedDateTime with its zone and convert it with toInstant(). Its compare() method orders exact times. When a local time is skipped or repeated during an offset transition, the API offers explicit disambiguation options, including reject. Resolve that ambiguity before importing comparable values. Temporal.ZonedDateTime conversion, comparison and disambiguation.

For all-day windows, keep the task in civil dates. A synthetic publication period covering November 10 through November 12 inclusively can be represented as [2026-11-10, 2026-11-13). iCalendar likewise uses the day after the last included day as an all-day event’s exclusive end. In Numbers, enter these boundaries with DATE formulas so their time components are consistent. RFC 5545’s multi-day event example.

Resolve boundary cases without changing the question

An overnight interval needs the next date on its ending value. A window from November 10 at 23:00 to November 11 at 01:00 is valid. Entering both endpoints on November 10 reverses it. Correct the date rather than swapping values automatically, because swapping could conceal an entry mistake.

If touching endpoints should count for your particular task, use AND(B2<=E2,D2<=C2) inside the G2 formula. Keep the positive-length input check unless you have separately defined how point events should behave. This changes the boundary policy and should be recorded beside the table.

That policy also matters when using native Mac APIs. Apple documents Foundation’s DateInterval as a closed interval, including both endpoints, and permits zero duration. It provides intersects and intersection(with:), but its closed-interval model differs from the iCalendar convention used here. For positive-duration calendar overlap, apply the explicit strict endpoint test rather than assuming an API’s intersection policy matches your task. DateInterval.

Before relying on a comparison, check three things: every endpoint has the intended date and time basis, both windows have positive length, and the touching-endpoint example produces your chosen result. With those decisions explicit, the same two comparisons handle partial overlap, full containment and identical windows.

Sources