Date & Time
Check if all dates in a column are in chronological order
Compare offset ranges A2:A4 and A3:A5 inside SUMPRODUCT to verify schedules never step backward and count out-of-order rows.
Offset-range pairwise comparison (A2:A4 vs A3:A5)
A date list is sorted chronologically if and only if no entry is later than the entry directly below it: Row 2 <= Row 3, Row 3 <= Row 4, and Row 4 <= Row 5.
Comparing the shifted slices A2:A4 > A3:A5 checks every adjacent pair simultaneously. Coercing TRUE/FALSE with -- inside SUMPRODUCT counts how many backward jumps exist; = 0 confirms chronological order.
Validate that every input is a numeric date before comparing. Blank cells and date-looking text do not form a valid chronological date series. If timestamps are supplied, the comparison also includes time of day.
| Row | MilestoneDate (A) | Adjacent Check vs Next Row |
|---|---|---|
| 2 | 2026-01-10 | 2026-01-10 <= 2026-02-15 (OK) |
| 3 | 2026-02-15 | 2026-02-15 <= 2026-02-15 (Same day OK) |
| 4 | 2026-02-15 | 2026-02-15 <= 2026-04-01 (OK) |
| 5 | 2026-04-01 | (end of list) |
Allowing vs rejecting same-day ties
Use > when two milestones on the same date are valid (non-decreasing order), or >= when dates must strictly increase with no ties.
=SUMPRODUCT(--(A2:A4 > A3:A5)) = 0Strictly increasing check and inversion count
Because rows 3 and 4 share 2026-02-15, strict >= flags a tie (FALSE).
=SUMPRODUCT(--(A2:A4 >= A3:A5)) = 0Try the example
Use the original values or change them to test your understanding.