Assume you have a date list that has different date formats, as seen in the accompanying picture. In this instance, google sheets’s Sort function will fail to sort them appropriately. However, you may convert all various date formats to a particular format, such as mm/dd/yyy, and then arrange them chronologically. This post will demonstrate how to sort dates in chronological order and how to check if a range of dates are in chronological order.

Explanation

This is an excellent illustration of how the SUMPRODUCT function may be used to overcome the inability of the COUNTIF or COUNTIFS functions to operate. In this scenario, the purpose is to verify that all dates within a certain range are in chronological order and to display a “YES” value only when dates are in chronological order.

The argument is pretty straightforward, although maybe not apparent. Rather of ensuring that all dates are bigger than the previous one, we determine if any prior date is greater than the next. If we discover even one, the dates will be out of sequence. They are if we discover none (zero).

At its heart, this formula uses the SUMPRODUCT function to determine the number of dates in A1:C1 that are larger than or equal to B1:D1:

=SUMPRODUCT(--(A1:C1>=B1:D1))

Due to the fact that we are employing the larger than or equal to (>=) operator, successive duplicate dates will be disallowed.

You can use the following formula based on the IF function, the SUMPRODUCT function, type:

=IF(SUMPRODUCT(--(A1:C1>=B1:D1)) =0,”YES”,””)

Related Functions

• Google Sheets SUMPRODUCT function
  The Google Sheets SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
• Google Sheets COUNTIF function
  The Google Sheets CHOOSE function returns a value from a list of values based on index. The syntax of the CHOOSE function is as below:=CHOOSE (index_num, value1,[value2],…) …
• Google Sheets IF function
  The Google Sheets IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
• Google Sheets COUNTIFS function
  The Google SheetsCOUNTIFS function returns the count of cells in a range that meet one or more criteria. The syntax of the COUNTIFS function is as below:= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)…