Find the minimum and maximum date in a list
 

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.

Find the minimum and maximum date in a list
 

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)
--
Gary''s Student - gsnu200814


"jeremiah" wrote:

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.

Find the minimum and maximum date in a list
 

=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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"jeremiah" wrote in message
...
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.

Find the minimum and maximum date in a list
 

Have you considered using MIN and MAX to get your dates?

Barb Reinhardt

"jeremiah" wrote:

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.

Find the minimum and maximum date in a list
 

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.

"Barb Reinhardt" wrote:

Have you considered using MIN and MAX to get your dates?

Barb Reinhardt

"jeremiah" wrote:

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.

Find the minimum and maximum date in a list
 

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

"jeremiah" wrote:

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.

"Barb Reinhardt" wrote:

Have you considered using MIN and MAX to get your dates?

Barb Reinhardt

"jeremiah" wrote:

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.