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)
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,846
Office Version
2010
Platform
Windows
If the dates are all Mondays then:

=MAX(A:A)

should return the latest Monday date.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,846
Office Version
2010
Platform
Windows
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?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,926
Office Version
2010
Platform
Windows
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.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,846
Office Version
2010
Platform
Windows
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:
 

bosco_yip

Well-known Member
Joined
Dec 2, 2002
Messages
1,938
Office Version
2019
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,503
Messages
5,468,994
Members
406,627
Latest member
IncandenzaH1997

This Week's Hot Topics

Top