Formula to check if dates are in chronological order?

I can't think of a formula to handle every case. A simple formula is

B1 formula = IF(A1>A2,"Out of Order","")

Fill down

The last row of the table will be an error due to an invalid reference.

As you fix ones that are marked, others might get marked. For example, if your table had 22.03.24 and 23.03.24 consecutively in the table, the first would not be marked but the second would. After fixing the second, the first would get marked.

The problem with all formulas is what I was getting at in my earlier post. If there is a block of dates out of order, which ones do you mark out of order.

Here is one that marks a date out of order if there is a smaller date below it in the table (of 1000 rows):

=IF(COUNTIF(A1:A$1000,"<"&A1)>0,"Out of Order","")

Here is one that marks a date out of order if there is a larger date above it in the table. It will not consider 22.03.24 to be out of order in your table but both 19.02.21 dates will be.

=IF(COUNTIF(A$1:A1,">"&A1)>0,"Out of Order","")

If you have these dates in this order,

1/1/24

1/4/24

1/5/24

1/2/24

1/3/24

Which ones are the ones not in order. Choices are

1. All dates below 1/1 are out of order (because all of them will move around if sorted)
2. 1/4 and 1/5 are out of order (because there are earlier dates lower in the table)
3. 1/2 and 1/3 are out of order (because there are later dates higher in the table)

Also, will the dates all be consecutive or will there be gaps?

The Excel example uses an Excel "array" formula that basically does an entire column of comparisons all within the one cell. Numbers does not have that capability.

If your columns are short and of fixed length and all cells have dates in them, you could do all the comparisons within one cell

=AND(A2>A3,A3>A4,...etc)

Result will be TRUE if it is in order, FALSE if not.

The formula will work if there are empty cells at the bottom of the column but it will have a blue warning triangle.

One advantage of doing the comparisons in an adjacent column vs in a single cell is you can quickly see where the problems are.

I am not sure a single formula that does a column of comparisons within one cell is any faster than a column of separate formulas. It might be. I recommend using Excel if speed is an issue. It is orders of magnitude faster than Numbers. And I do mean orders of magnitude; I am not exaggerating. For smaller spreadsheets the difference is not noticeable but for huge ones with lots of formulas, a spreadsheet that Excel can recalculate in a split second will take Numbers several minutes or even longer. If your spreadsheet is too slow in Numbers, it might not be the right app to be using.

I have an excel document with up to 1000 rows all containing data over various columns. All I need is a formula to run from A1 to A1000 and show / highlight dates that are out of chronological order. I want to check that row and decide if row should move or if date should be changed. No sorting options please.. I need to be able to only see where dates are not in chronoligal order. I can add columns if needed and delete afterwards when done. Hope there is a solution coz cheching 1000 rows is time consuming and still have errors.

it would look like this only running over couple of years

I need formula to hightlight that 22.03.24 is out of sequence - i can give a starting date and end date

THANKS!!!! This is super and solved my problem. Thanks again.