Find the minimum and maximum date in a list

  • Thread starter Thread starter jeremiah
  • Start date Start date
J

jeremiah

My datasheet includes 4K plus records that each have a date associated with
them - the dates can be duplicated throughout. I am trying to create a
report to total all of those records and in the heading I need to have the
date range. So I need to search the date column and find the earliest date
and the latest date and copy just those dates to my header on my totals
worksheet. How can I find and copy those dates to a new worksheet? Have
searched and can't seem to find just the right hint.
 
Say the dates (unsorted) are in column A in Sheet1. In Sheet2:

=MAX(Sheet1!A:A) for the maximum
=MIN(Sheet1!A:A) for the minimum

REMEMBER to format these cells as dates. (Excel is not smart enough to do it
by itself)
 
=MIN(Sheet1!A:A) finds lowest date in column A of Sheet1
=MAX(Sheet1!A:A) finds largest
Dates are nothing but numbers formatted in a special way
 
Yes, I had considered but was looking for a macro solution. Was trying to
avoid having any formulas on the worksheet itself. The totals are being
pulled from a filtered list so I either needed to create a report template or
let a macro do it's trick and add the header to the filtered list. This will
work, just looking for alternative solutions.
 
Sub findDates()
Dim rng As Range
Dim minMyDate As Date
Dim maxMyDate As Date

Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
maxMyDate = Evaluate("=MAX(" & rng.Address & ")")
minMyDate = Evaluate("=MIN(" & rng.Address & ")")

MsgBox " MIN DATE IS " & minMyDate & vbCrLf & "MAX DATE IS " &
maxMyDate
End Sub
 
Back
Top