select latest Monday from the column.

vijaybhatt

New Member
Joined
Dec 23, 2015
Messages
17
I have column with containing Below data.
9 Nov
9 Nov
9 Nov
9 Nov
16 Nov
16 Nov
16 Nov
16 Nov
16 Nov
21 Nov
21 Nov
21 Nov
21 Nov
21 Nov
26 Nov
26 Nov
26 Nov
26 Nov
26 Nov
26 Nov
these dates are Monday of every week.
I want to filter latest Monday (which is 26 Nov)
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
If the dates are all Mondays then:

=MAX(A:A)

should return the latest Monday date.
 
Upvote 0
Try it like this...
Code:
[A:A].AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(2, Format([MAX(A:A)],"m/d/yyyy"))
Rick, I'm curious about the Criteria2 here.

Criteria2:=Array(2, Format([MAX(A:A)],"m/d/yyyy"))

A quick test shows replacing the 2 with a 1 doesn't work, but replacing the 2 with either 3, 4, or 5 does work, then 6 fails again.

Its not obvious to me why the 2 is needed in any case, although it shows up if I simply record the filtering action. Can you enlighten me?
 
Upvote 0
Rick, I'm curious about the Criteria2 here.

Criteria2:=Array(2, Format([MAX(A:A)],"m/d/yyyy"))
I cannot help you with this... I got the base code line from the Macro Recorder and tweak it slightly for the OP's setup.
 
Upvote 0
I cannot help you with this... I got the base code line from the Macro Recorder and tweak it slightly for the OP's setup.
I'm guessing that it has something to do with the multi-tier structure you see when you click the autofilter dropdown for the list of dates. Something like:
Select All
Year
Month
Days

but it sure isn't intuitive! :cool:
 
Upvote 0
Or try this

1] Using formula in Conditional Formatting on the date column and choose the background color

Formula : =MAX(A:A)=A2

2] To filter latest Monday by filter on color.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

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
Back
Top