Date Range displayed in header

[Guest]

I am attempting to put the range of MIN and MAX dates pulled from a column. I
would like it to look like:

Date Range: 9/6/2006 - 10/26/2006

from a column like this:

Date
09/06/2006
09/06/2006
09/10/2006
10/12/2006
10/26/2006

Thanks in advance
BD

 

[Guest]

=CONCATENATE("Date Range": ",MIN([range])," - ",MAX([range]))

Note the space after the colon and before and after the hyphen,
elseeverythingwillgetsquashedtogetherlikethis.

Dave

 

[Dave Peterson]

=text(min(a1:a10),"m/d/yyyy") & " - " & text(max(a1:a10),"m/d/yyyy")

 

[Guest]

Thanks for the quick reply.
I couldn't get it to work as typed, error. However, if I modify it will at
least not give error, but formatting isn't correct. It only works in the
worksheet though, if in header only shows as text.

I modified like this:
=CONCATENATE("Date Range: " & MIN(A:A)," - " & MAX(A:A))
and get this:
Date Range: 38995.7438773148 - 39007.7204976852

Cell is set to date mm/dd/yy

=CONCATENATE("Date Range": ",MIN([range])," - ",MAX([range]))

Note the space after the colon and before and after the hyphen,
elseeverythingwillgetsquashedtogetherlikethis.

Dave
--
Brevity is the soul of wit.

I am attempting to put the range of MIN and MAX dates pulled from a column. I
would like it to look like:

Date Range: 9/6/2006 - 10/26/2006

from a column like this:

Date
09/06/2006
09/06/2006
09/10/2006
10/12/2006
10/26/2006

Thanks in advance
BD

 

[Guest]

Thanks for the reply. That works great in a cell on the worksheet, but how do
I get it to display in the header?

Thanks in advance.
BD

 

[Dave Peterson]

A header like a print header?

You have to do it manually or use a macro.

Rightclick on the Excel icon to the left of the File dropdown on the worksheet
menu bar.
Select view code and paste this into the code window:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim myMin As Date
Dim myMax As Date
Dim myStr As String
Dim myRng As Range

With Worksheets("sheet9999")
Set myRng = .Range("a:a")
myMin = Application.Min(myRng)
myMax = Application.Max(myRng)
myStr = "Date Range: " & Format(myMin, "mm/dd/yyyy") _
& " - " & Format(myMax, "mm/dd/yyyy")
.PageSetup.LeftHeader = myStr
End With
End Sub

Change sheet9999 to the correct name.

 

[Guest]

That worked great. Thanks for the help

BD

A header like a print header?

You have to do it manually or use a macro.

Rightclick on the Excel icon to the left of the File dropdown on the worksheet
menu bar.
Select view code and paste this into the code window:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim myMin As Date
Dim myMax As Date
Dim myStr As String
Dim myRng As Range

With Worksheets("sheet9999")
Set myRng = .Range("a:a")
myMin = Application.Min(myRng)
myMax = Application.Max(myRng)
myStr = "Date Range: " & Format(myMin, "mm/dd/yyyy") _
& " - " & Format(myMax, "mm/dd/yyyy")
.PageSetup.LeftHeader = myStr
End With
End Sub

Change sheet9999 to the correct name.