PDA

View Full Version : Solved: Max and Min dates in right header



Djblois
02-14-2007, 09:29 AM
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?

xld
02-14-2007, 10:16 AM
=TEXT(MIN(A:A),"dd\\mm\\yy")&"-"&TEXT(MAX(A:A),"dd\\mm\\yy")

gnod
02-14-2007, 10:24 AM
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
02-14-2007, 10:40 AM
Yes - I want to put it in the right header

mdmackillop
02-14-2007, 11:06 AM
Modifying Bob's formula, you could add the header by VBA prior to printing
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

Djblois
02-14-2007, 12:51 PM
I just need help with one more thing the row with dates is on a seperate tab that I have set to detail

mdmackillop
02-14-2007, 01:01 PM
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

Djblois
02-14-2007, 01:09 PM
That worked madmackillop but can I add formating to it?

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

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

mdmackillop
02-14-2007, 01:22 PM
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

Djblois
02-14-2007, 02:39 PM
Thank you you are the best