Nov 08 2022 07:32 AM
I need help sorting data by order date. I have over 100K lines in two columns with one showing the part number and the other showing the order date. I want to know how many instances there were where we went 2 years or more in-between the purchase of an item.
Nov 08 2022 09:54 AM
SolutionThis solution uses sorting and conditional formatting.
1. Sort by Part (A to Z) then by Order Date (Old to New).
2. Use conditional formatting w/formula to identify instances where the part ordered has been 2+ years since previous order:
=AND($A2=$A1,DATEDIF($B1,$B2,"y")>=2)
Nov 08 2022 10:20 AM
Nov 08 2022 10:50 AM
Nov 08 2022 11:40 AM
Quick follow up. With that formula being used, it is highlighting the order from 2 years ago. How can i tweak the formula so it highlights the order that was placed after its been two years? @Patrick2788
Nov 08 2022 12:40 PM
Nov 08 2022 01:07 PM
Yes and that is what i want. I am just looking for the formula to highlight the order that is greater than or equal to two years. Currently it is highlighting the older of the orders. Below I've tried to show an example...currently it is highlighting the blue row, i want it to highlight the orange row which is the order two years or greater in the future. I'm sorry for nagging you about this, I really am appreciative of your help here. The file I have has 300K thousand lines which makes this a huge help if i can get this to work.
2/7/2016---AE5554C
2/7/2016---AE5554C
9/7/2017---AE5554C
4/8/2018---AE5554C
11/10/2014---AE6664B
11/13/2013---AE1101B
12/17/2017---AE1101B
2/10/2018---AE1101B
2/10/2018---AE1101B
2/10/2018---AE1101B
Nov 08 2022 01:40 PM
That makes sense. Here's the revision to the formula:
=OR(IFERROR(AND($A2=$A1,DATEDIF($B1,$B2,"y")>=2),0),IFERROR(AND($A2=$A3,DATEDIF($B2,$B3,"y")>=2),0))
Nov 08 2022 09:54 AM
SolutionThis solution uses sorting and conditional formatting.
1. Sort by Part (A to Z) then by Order Date (Old to New).
2. Use conditional formatting w/formula to identify instances where the part ordered has been 2+ years since previous order:
=AND($A2=$A1,DATEDIF($B1,$B2,"y")>=2)