VBA help - extract Max and Min date from a Range

[Mallesh23]

Hi Team,

How to find max date and min date from a Range. data is in text format.

Expected output is in Column D.

Below is a Data with output.

Book6
	A	B	C	D
1	Date
2	01/02/18		MinDate	01/02/18
3	02/05/18		MaxDate	08/04/20
4	03/28/18
5	08/04/20
6	05/26/20
7	06/13/20
Sheet1

Thanks
mg

 

[Joe4]

If they are in Text format, enter these formulas as arrays (use "CTRL+SHIFT+ENTER" instead of just enter to enter them; note the squiggly brackets are not really there, they just indicate it is an array formula).

MIN:

{=MIN(DATEVALUE(A2:A7))}

MAX:

{=MAX(DATEVALUE(A2:A7))}

 

[Mallesh23]

Hi Joe4,

I tried it, but not working. what if date column is in text and not in text.

I am looking help in vba, so that I want to use it in autofilter , greater than max date etc.

Sub test()
Dim str As Variant

Dim rng As Range
Set rng = Range("A2:a7")

Range("g1").FormulaArray = "=MIN(DATEVALUE(rng))"
str =   "=MIN(DATEVALUE(rng))"

End Sub

thanks
mg

 

[Joe4]

What are your dates in Text?
As long as we are using VBA, can we change them to actual Dates so they are easier to work with?

 

[Mallesh23]

Hi Joe,

I have converted into numbers.

Now How to get max and min date from it via vba.

Book1
	A	B	C	D	E
1	Date		IS Number
2	01/02/2018		TRUE
3	02/05/2018		TRUE	max	09/04/2020
4	02/05/2018		TRUE	Min	01/02/2018
5	08/04/2020		TRUE
6	09/04/2020		TRUE
7	08/04/2020		TRUE
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=ISNUMBER(A2)

Thanks
mg