All dates in chronological order

This is a good example of how the SUMPRODUCT function can help in situations where the COUNTIF or COUNTIFS functions do not work. In this case, the goal is to check all dates in a given range and show a check mark (✓) only when dates are in chronological order.

The logic itself is quite simple, but perhaps not intuitive. Rather than check that all dates are greater than the previous date, we check if any previous date is greater than the next date. If we find even one, the dates are not in order. If we find none (zero), they are.

At the core, this formula is based on the SUMPRODUCT function, which counts the number of dates in A5:E5 that are greater than or equal to B5:F5:

SUMPRODUCT(--(A5:E5>=B5:F5)) // count invalid dates

Note we are using the greater than or equal to (>=) operator, we will disallow consecutive duplicate dates.

This is an array operation where both ranges contain five dates. The result is an array of TRUE and FALSE values like this:

{FALSE,FALSE,FALSE,FALSE,FALSE}

Each FALSE signifies a date in B5:F5 that is not greater or equal to the preceding date. Notice the range A5:E5 deliberately includes empty cells in column A, which has the practical effect of treating any blank dates in column B as invalid. But it also requires that relevant cells in column A be empty.

The next step is to coerce the TRUE and FALSE values into 1s and 0s so they can be counted. There are a number of ways to do this in Excel, but in this case we use a double negative (--):

--({FALSE,FALSE,FALSE,FALSE,FALSE}} // returns {0,0,0,0,0}

which returns an array of 5 zeros. This array is returned to the SUMPRODUCT function, which sums the array:

SUMPRODUCT({0,0,0,0,0}) // returns zero

Because the result we want is zero (i.e. no invalid dates) we test for zero, which returns TRUE:

=SUMPRODUCT({0,0,0,0,0})=0
=0=0
=TRUE

This result is returned directly to the IF function as the logical test.

=IF(TRUE,"✓","")
="✓"

IF then returns a check mark since all dates are in order. The way the formula is structured, any number of dates out of sequence will cause the test to return a number greater than zero, and the IF function to return an empty string ("") instead of a check mark ("✓"). For example, in row 8, which has one date out of sequence, the formula evaluates like this:

=IF(SUMPRODUCT(--(A8:E8>=B8:F8))=0,"✓","")
=IF(SUMPRODUCT(--({FALSE,FALSE,TRUE,FALSE,FALSE}))=0,"✓","")
=IF(SUMPRODUCT({0,0,1,0,0})=0,"✓","")
=IF(1=0,"✓","")
=IF(FALSE,"✓","")
=""

Although this example has just five dates in each row, the same approach will scale up to any number of dates.

With SORT

In Excel 365, the SORT function provides a nice alternative solution:

=IF(SUM(--(B5:F5<>SORT(B5:F5,1,1,1)))=0,"✓","")

In this version, the dates in B5:F5 are compared to the same dates, after sorting with SORT. Similar to the original formula above, we are counting any case where a date is not the same (i.e. any date moved by SORT). If we find zero dates that are different, the check mark is returned.

Unlike the original formula, the SORT version won't automatically check for blank (empty) cells or for duplicate dates. The version below adds this additional check for blank cells:

=IF(SUM(((B5:F5<>SORT(B5:F5,1,1,1)))+(B5:F5=""))=0,"✓","")

And this version checks for both empty and duplicate values:

=IF(SUM(((B5:F5<>SORT(B5:F5,1,1,1)))+(B5:F5="")+(A5:E5=B5:F5))=0,"✓","")

The addition (+) operator works like OR logic in Boolean Algebra. The math operation automatically coerces TRUE and FALSE values to 1s and 0s, so we no longer need the double negative (--).

Note: we use the SUM function here instead of SUMPRODUCT because the dynamic array support in Excel 365 means SUM can handle the array operation natively, without control + shift + enter.

Check mark character (✓)

Inserting a check mark character in Excel can be tricky. The easiest way to get the check mark character (✓) used in this formula into Excel is simply to copy and paste it into the formula bar. If you have trouble, you can use the UNICHAR function like this:

=UNICHAR(10003) // returns "✓"

The original formula can then be written like this:

=IF(SUMPRODUCT(--(A5:E5>=B5:F5))=0,UNICHAR(10003),"")

and return the same results. The UNICHAR function was introduced in Excel 2013.

Conditional Formatting

You can also use conditional formatting to highlight invalid dates. In the example shown, dates that are out of sequence are highlighted with a simple rule, based on this formula:

=B5<=A5

The rule is applied to all dates in the range B5:F15.