asked on

Select minimum date for the month in a list of dates.

I want to select the least date of each dates per month in a range.

e.g. My column A has dates

16Aug18
16Aug18
16Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
14Aug18
14Aug18
14Aug18
14Aug18
14Aug18
14Aug18
14Aug18
14Aug18
14Aug18
14Aug18
14Aug18
14Aug18
14Aug18
14Aug18
14Aug18
13Aug18
13Aug18
13Aug18
11Aug18
10Aug18
10Aug18
10Aug18
10Aug18
9Aug18
9Aug18
8Aug18
8Aug18
8Aug18
8Aug18
8Aug18
8Aug18
7Aug18
7Aug18
6Aug18
2Aug18
1May18
16Apr18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Apr18
15May18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
14Aug18
14Aug18
13Aug18
13Aug18
13Aug18
13Aug18
13Aug18
13Aug18
10Aug18
10Aug18
10Aug18
9Aug18
9Aug18
9Aug18
9Aug18
9Aug18
8Aug18
8Aug18
8Aug18
8Aug18
8Aug18
8Aug18
8Aug18
8Aug18
7Aug18
7Jun18
7Jul18
7Apr18
6Apr18
6Aug18
4Aug18
4Aug18
3Aug18
3Aug18
3Aug18
2Aug18
2Aug18
2Aug18
2Jun18
2Aug18
2Aug18
1Aug18
1Jul18
1Aug18
1Aug18

I want to select the least dates for Apr, May, Jun, Jul and Aug

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Subodh Tiwari (Neeraj)

Answered the original question.

Saravanan Ayyappan

ASKER

Hi Subodh,

Thank you for the help.

Also, I have found two more solutions.

Assume the values in column 'A' are listed from row 2 to row 500.

Split the date value alone in Column B, Month in Column C and Year in Column D.

1) =MIN(B2:B500")
2) "=SUBTOTAL(5,B2:B5000")

Sheets(sheetname).Range("U2").Value = "=MIN(R2:R" & NN & ")"
Sheets(sheetname).Range("A1:D500").AutoFilter Field:=3, Criteria1:="<<criteria>>" & "*"
Sheets(sheetname).Range("U3").Value = "=SUBTOTAL(5,B2:B" & NN & ")"