Flag Out of Sequence Dates in an a Column

strusty

New Member
Joined
Sep 24, 2014
Messages
2
I have Excel 2010 on Windows 7 Professional and need help with a report that I run daily. I think I am proficient in Excel, but haven't been able to figure this one out. I check Microsoft Help but no luck.

So here is the issue.

I have various clusters of dates in column O that indicates the date we received the product in. Each Row is a different storage bin. Each cluster is a different item which is separated by an empty row. I want to be able to flag my date column when the dates are out of order. Is there a formula I can use or can I use conditional formatting or anything else I am not thinking about that will help me flag out of sequence receipt dates??? Example below...

ITEM 1QTYWTDT RECD
BIN 11/2/14
BIN 22/1/14
BIN 33/5/13
ITEM 2
BIN 41/2/14
BIN 512/9/13
ITEM 3
BIN 65/4/14
ITEM 4
BIN 79/10/14
BIN 89/10/14
BIN 98/9/14
BIN 109/10/14

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,007
So, in your example which rows do you want flagged? And why?

I have Excel 2010 on Windows 7 Professional and need help with a report that I run daily. I think I am proficient in Excel, but haven't been able to figure this one out. I check Microsoft Help but no luck.

So here is the issue.

I have various clusters of dates in column O that indicates the date we received the product in. Each Row is a different storage bin. Each cluster is a different item which is separated by an empty row. I want to be able to flag my date column when the dates are out of order. Is there a formula I can use or can I use conditional formatting or anything else I am not thinking about that will help me flag out of sequence receipt dates??? Example below...

ITEM 1QTYWTDT RECD
BIN 11/2/14
BIN 22/1/14
BIN 33/5/13
ITEM 2
BIN 41/2/14
BIN 512/9/13
ITEM 3
BIN 65/4/14
ITEM 4
BIN 79/10/14
BIN 89/10/14
BIN 98/9/14
BIN 109/10/14

<tbody>
</tbody>
 

suremac

New Member
Joined
Jan 27, 2014
Messages
49
You could use this,
Code:
=IF( AND(O2 <> "", O2-O1<0), 1, 0)
, starting in row two of any column (except those with data in it). It will place a value of 1 in the row adjacent to the date that is out of order. Then, you could highlight rows with 1 in them.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,491
Messages
5,523,255
Members
409,506
Latest member
reneekeane

This Week's Hot Topics

Top