Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date formula in printing (Office 97 / Office XP)

    I have a spreadsheet that I update and print weekly. In the footer, I have entered: "As of: &[Date]" which of course returns the value of today's date. Is there a way (other than manually) that I can have it always be the previous Saturday's date, no matter when I update it during the week? Thanks!
    Louise

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Date formula in printing (Office 97 / Office XP)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Louise

    Yes there is always a way to do something. It is called VBA. If you are familiar with VBA make sure that you add the code to the workbook_BeforePrint Event.

    If you are not sure of what VBA is all about, well here is a nudge...

    <font color=red>
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    '/This example does it on a specific sheet.
    If ActiveSheet.Name = "Sheet1" Then
    With ActiveSheet.PageSetup
    .RightFooter = "As of saturday " & _
    Date - Application.WorksheetFunction.WeekDay(Date)
    End With
    End If

    '/This example does it on all sheets.
    ' Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
    ' With ActiveSheet.PageSetup
    tab] .RightFooter = "As of saturday " & _
    Date - Application.WorksheetFunction.WeekDay(Date)

    ' End With
    End Sub
    </font color=red>


    PS maybe there is a better way to calculate the Saturday date, I just gave one example.
    HTH

    Wassim [compute}
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date formula in printing (Office 97 / Office XP)

    You will need to put some code like this in the Workbook BeforePrint routine. You would need to do this for all sheets that need the date in the footer.

    <pre>Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Worksheets("Sheet1").PageSetup.CenterFooter = "As of " & Format(Now() - Weekday(Now()) Mod 7, "mmmm d, yyyy")
    End Sub
    </pre>

    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date formula in printing (Office 97 / Office XP)

    Hi! Thanks for the replies. Unfortunately I don't know anything about VBA.
    Another thought I had was perhaps it would be better to enter a formula in a cell at the bottom of the sheet and not use a footer at all. If there is such a formula, this would work as well?
    Thanks!
    Louise

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date formula in printing (Office 97 / Office XP)

    The Formula would be:

    <pre>=NOW()-WEEKDAY(NOW())
    </pre>

    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date formula in printing (Office 97 / Office XP)

    Perfect! Thank you very much!
    Louise

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •