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?
Bob Phillips
02-14-2007, 10:16 AM
=TEXT(MIN(A:A),"dd\\mm\\yy")&"-"&TEXT(MAX(A:A),"dd\\mm\\yy")
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.