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>
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,699
Messages
5,833,204
Members
430,196
Latest member
rez5656

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top