Solved: Max and Min dates in right header

[Djblois]

I was wondering if it is possible to do a formula to put the min to max date in the right header from a column. I want it to look in my date column and then use the minimum date and maximum date to look like this (01\01\07-01\10\07). Is this possible?

[Bob Phillips]

=TEXT(MIN(A:A),"dd\\mm\\yy")&"-"&TEXT(MAX(A:A),"dd\\mm\\yy")

[gnod]

I was wondering if it is possible to do a formula to put the min to max date in the right header from a column. I want it to look in my date column and then use the minimum date and maximum date to look like this (01\01\07-01\10\07). Is this possible?

are you referring to the header & footer??

[Djblois]

Yes - I want to put it in the right header

[mdmackillop]

Modifying Bob's formula, you could add the header by VBA prior to printing
[VBA]Private Sub Workbook_BeforePrint(Cancel As Boolean)
AddHeader
End Sub

Sub AddHeader()
With Application.WorksheetFunction
ActiveSheet.PageSetup.RightHeader = _
.Text(.Min(Columns(1)), "dd\\mm\\yy") & "-" & .Text(.Max(Columns(1)), "dd\\mm\\yy")
End With
End Sub

[/VBA]

[Djblois]

I just need help with one more thing the row with dates is on a seperate tab that I have set to detail

[mdmackillop]

[VBA]Sub AddHeader()
Dim ws As Worksheet
Set ws = Sheets("detail")
With Application.WorksheetFunction
ActiveSheet.PageSetup.RightHeader = _
.Text(.Min(ws.Columns(1)), "dd\\mm\\yy") & "-" & .Text(.Max(ws.Columns(1)), "dd\\mm\\yy")
End With
End Sub
[/VBA]

[Djblois]

That worked madmackillop but can I add formating to it?

[VBA]ActiveSheet.PageSetup.RightHeader = .text(.Min(Columns(4)), "mm\\dd\\yy") & "-" & .text(.Max(Columns(4)), "mm\\dd\\yy")
ActiveSheet.PageSetup.RightHeader = "&""Arial,Bold""&12"[/VBA]

this doesn't work it just clears out the right header

[mdmackillop]

[VBA]Sub AddHeader()
Dim ws As Worksheet, HeadTxt As String
Set ws = Sheets("detail")

With Application.WorksheetFunction
HeadTxt = .Text(.Min(ws.Columns(1)), "dd\\mm\\yy") & "-" & .Text(.Max(ws.Columns(1)), "dd\\mm\\yy")
End With
ActiveSheet.PageSetup.RightHeader = "&""Arial,Bold""&12 " & HeadTxt
End Sub
[/VBA]

[Djblois]

Thank you you are the best