Notion dateBetween Off by One Day: Find the Right Counting Rule

If Notion’s dateBetween() result is one day short, decide what the number should count before you change the formula. The function returns the difference between two dates, while a count that includes both dates answers a different question. Adding 1 is the rule for that inclusive count only; it isn’t a general repair for timestamps, timezones, or reversed inputs.

I would start with two fixed dates, then bring the clock back into the calculation. That separates a counting-rule mismatch from a time-dependent result without changing the meaning of the source data.

Choose the counting rule first

Notion defines dateBetween() as the difference between two dates in a specified unit. It does not describe it as an inclusive count of the dates on a calendar.

Consider an event that starts September 21 and ends September 22. The date labels are one day apart. If attendance on both September 21 and September 22 counts, the event occupies two calendar dates. Neither number is a substitute for the other.

A deadline countdown usually needs the difference. An attendance or occupancy count may need both endpoints. A timed reservation needs the actual start and end times.

Inspect the two inputs

Open the actual source date cells, not just the formula’s displayed result. Notion’s Date property can hold a date, a range, and an optional time. In the date picker, End date adds a range, Include time adds an exact time, and Date format & timezone controls those settings.

1. Confirm both values are dates. A rollup can return a date or a list depending on its configuration. A formatted date string is text. If the editor reports a type error, follow the date, text, and list diagnostic before interpreting the count.
2. Check for a missing input. A blank End might mean “not entered” or “still ongoing.” Decide which meaning applies; substituting today changes the question the formula answers.
3. Check the order. For a forward duration, put End first and Start second. The official range examples return 365 for end minus start and -365 for the reverse order. A negative result can be a valid direction indicator.
4. Inspect times and endpoints. Two columns named Start and End are different from one Date range. Make sure the formula references the values you intended.

Keep meaningful timestamps in the source properties. If you want to compare date-only inputs, use separate scratch rows or properties rather than stripping times from records that also support reminders, schedules, or other formulas.

Use the formula that matches your properties

For two populated Date properties named Start and End, this is the basic difference expression:

dateBetween(prop("End"), prop("Start"), "days")

If both endpoints live in a single property named Dates, select them explicitly:

dateBetween(
  dateEnd(prop("Dates")),
  dateStart(prop("Dates")),
  "days"
)

The documented roles of dateStart() and dateEnd() are to return the start and end of a range. The reference doesn’t describe dateStart() as a way to reset a timestamp to midnight, so wrapping now() in it doesn’t establish a calendar-day or daylight-saving fix.

Add one only when both endpoints count

For complete date-only inputs with End on or after Start, the inclusive counting pattern is:

dateBetween(prop("End"), prop("Start"), "days") + 1

The extra one counts the starting date. It does not round a partial day, correct an imported timestamp, or count working days. If End precedes Start, correct the data or define a deliberate signed rule before applying an inclusive count. I would flag the reversed interval rather than use abs() to hide it.

For a readable Formula property that flags missing and reversed inputs, the following documentation-based example makes every branch return text:

if(
  or(empty(prop("Start")), empty(prop("End"))),
  "Add both dates",
  if(
    prop("End") < prop("Start"),
    "End precedes Start",
    format(dateBetween(prop("End"), prop("Start"), "days") + 1)
  )
)

format() turns the count into text, so this example gives you a label rather than a numeric column for sums. If you need numeric calculations, keep the numeric expression and add a separate missing/reversed-input flag. Notion’s function reference documents the conditional, emptiness, comparison, and text-formatting operations used here.

Check the source dates for emptiness, not the calculated difference: Notion treats 0 as empty for empty(), and zero is a legitimate same-date difference. Formulas inside automations have additional return-type requirements; Notion also warns that operating on an undefined date can pause an automation.

When the clock or timezone matters

A formula using now() compares against the current date and time. For a date-based countdown, I’d start from today() because it returns the current date without the time:

dateBetween(prop("Due"), today(), "days")

That example assumes a populated, date-only Due property and asks for the difference from today; it doesn’t include today as an extra occupied date. If Due contains a meaningful hour, keep that timestamp and use now() for a timed deadline instead. Swapping only one operand for today() won’t remove the time from the other operand.

For imported dates, inspect the original value as well as the date shown in Notion. A date-only value and a timestamp with a UTC offset are different inputs. Notion’s own parseDate() examples distinguish a plain ISO date from midnight UTC: 2022-01-01 is shown as January 1, while 2022-01-01T00:00Z is shown as December 31 at 4:00 PM in the documentation’s example. That is a timezone boundary, not evidence that a day should be added.

When a timed result looks wrong, record the exact two timestamps and the date picker’s timezone. Notion’s formula reference doesn’t spell out a complete rounding or daylight-saving contract for dateBetween(..., "days"), so a result from date-only examples can’t settle what happens across a clock change.

If you need actual elapsed time, calculate it directly. For two populated timestamped dates, this arithmetic converts their Unix timestamps in milliseconds into elapsed hours:

(timestamp(prop("End")) - timestamp(prop("Start"))) / 3600000

The divisor is the number of milliseconds in an hour. Dividing that result by 24 defines a duration in 24-hour blocks; it does not define how many local calendar dates an event touches. Choose the unit because it fits the task, not because it happens to match a troublesome row.

Check the cause before replacing the formula

Use a scratch row with fixed, date-only inputs on the same date, then on adjacent dates. Compare the result with the rule you chose above. Next inspect the problematic row’s time, range endpoints, and timezone. Only after the fixed-input comparison makes sense, restore the moving input: now() or today(). The formula editor shows a preview for the row from which you open it, which lets you inspect each case in your own database.

For the text-label expression above, keep a small comparison sheet beside the scratch rows. The following expected labels are derived from its branches, using September 2026 date-only inputs.

If the same-date row displays Add both dates, check that the emptiness condition references Start and End rather than dateBetween’s zero result. If the reversed row becomes positive, look for an absolute-value conversion or swapped property references. Once these fixed cases match your chosen rule, inspect the original timed or imported row without changing its source values.

If the mismatch appears only after you restore the current moment, investigate the clock dependency. If it follows one imported row, investigate that row’s source value and timezone. If every complete date-only interval is short by exactly the starting date you meant to count, apply the inclusive rule. Keep these diagnoses separate: a blanket + 1 can make one row look right while changing every other row’s meaning.