VBA help - extract Max and Min date from a Range

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
832
Office Version
  1. 2010
Platform
  1. Windows
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
ABCD
1Date
201/02/18MinDate01/02/18
302/05/18MaxDate08/04/20
403/28/18
508/04/20
605/26/20
706/13/20
Sheet1


Thanks
mg
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,520
Office Version
  1. 365
Platform
  1. Windows
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:
Excel Formula:
{=MIN(DATEVALUE(A2:A7))}

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

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
832
Office Version
  1. 2010
Platform
  1. Windows
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.



VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,520
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 4, 2009
Messages
832
Office Version
  1. 2010
Platform
  1. Windows
Hi Joe,

I have converted into numbers.

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

Book1
ABCDE
1DateIS Number
201/02/2018TRUE
302/05/2018TRUEmax09/04/2020
402/05/2018TRUEMin01/02/2018
508/04/2020TRUE
609/04/2020TRUE
708/04/2020TRUE
Sheet1
Cell Formulas
RangeFormula
C2:C7C2=ISNUMBER(A2)


Thanks
mg
 

Watch MrExcel Video

Forum statistics

Threads
1,119,220
Messages
5,576,803
Members
412,748
Latest member
MikeyP14
Top