Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Star Lounger
    Join Date
    Nov 2004
    Location
    San Diego, California, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Fixing Today's Date in Cell (2000)

    I would like to capture today's date in a cell and then keep it permanently so that it acts as a time stamp - but just the mm/dd/yy format. Right now I have a worksheet that uses the TODAY() function to graph a deadline in the same workbook, but it obviously changes the graph if I open it at a later date.

    I thought that I saw a thread with some neat "date" tricks recently (i.e., in the last month), but I can't find it now.

    Does anyone know of a way to accomplish this, or of the recent thread?

    Thank you!
    Don Liebman
    San Diego, CA 92115

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

    Re: Fixing Today's Date in Cell (2000)

    To insert today's date into a cell so that it will stay the same, hold down the Ctrl key and press the semicolon ([img]/forums/images/smilies/wink.gif[/img] key.

    Does that do what you need?
    Legare Coleman

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Fixing Today's Date in Cell (2000)

    Press Ctrl+semicolon to enter the current date in a cell as a fixed value. Ctrl+Shift+semicolon will enter the current time.

  4. #4
    Star Lounger
    Join Date
    Nov 2004
    Location
    San Diego, California, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fixing Today's Date in Cell (2000)

    Thank you - both!

    Now, is there a way to also enter these keystrokes into a macro, replacing the date in quotes in the following code, so that I can reuse this at will?

    Range("G14").Select
    ActiveCell.FormulaR1C1 = "1/19/2005"

    Many thanks!
    Don Liebman
    San Diego, CA 92115

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Fixing Today's Date in Cell (2000)

    Try :

    Activecell.FormulaR1C1=Date() ... If you are after the current date!
    Regards,
    Rudi

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Fixing Today's Date in Cell (2000)

    If you want today's date as a static use the line:
    <pre>Activecell=Date</pre>


    Steve

  7. #7
    Star Lounger
    Join Date
    Nov 2004
    Location
    San Diego, California, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fixing Today's Date in Cell (2000)

    Rudi and Steve,

    Thank you - that works great!

    Don
    Don Liebman
    San Diego, CA 92115

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

    Re: Fixing Today's Date in Cell (2000)

    I have the code below in my Personal.xls workbook and attached to a button on my toolbar:

    <pre>Sub InsertDateTime()
    ActiveCell.Value = Now()
    ActiveCell.Offset(0, 1).Select
    End Sub
    </pre>


    When I click on the button it inserts the date and time into the active cell. You can then format the cell to display the date, the time, or the date and time.
    Legare Coleman

  9. #9
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fixing Today's Date in Cell (2000)

    The code for inserting time and date into an active cell is great. However, is there away to put it into the following code which inserts path/name and page number in the footer? Thanks in advance.

    Sub InsertFooter()
    '
    ' InsertFooter Macro
    ' Macro recorded 09-12-2004
    '
    ' Keyboard Shortcut: Ctrl+Shift+F
    '
    Range("G7").Select
    With ActiveSheet.PageSetup
    .PrintTitleRows = ""
    .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = "&Z&F"
    .CenterFooter = ""
    .RightFooter = "&N"
    .LeftMargin = Application.InchesToPoints(0.75)
    .RightMargin = Application.InchesToPoints(0.75)
    .TopMargin = Application.InchesToPoints(1)
    .BottomMargin = Application.InchesToPoints(1)
    .HeaderMargin = Application.InchesToPoints(0.5)
    .FooterMargin = Application.InchesToPoints(0.5)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    .PrintQuality = 600
    .CenterHorizontally = False
    .CenterVertically = False
    .Orientation = xlPortrait
    .Draft = False
    .PaperSize = xlPaperLetter
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = 100
    .PrintErrors = xlPrintErrorsDisplayed
    End With
    End Sub

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Fixing Today's Date in Cell (2000)

    For example:

    .CenterFooter = Format(Date, "mmmm d, yyyy")

  11. #11
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fixing Today's Date in Cell (2000)

    What changes would have to be made to the vba in order to have the footer appear on each sheet of a spreadsheet with the appropriate page number on each sheet.? For example, sheet 1 would be 1, sheet 2, 2, sheet 3, 3, etc.

    When I attempted to do this with 3 sheets, the footer appears only on the first sheet, but the page number on the first sheet reads 3. Footers do no show on sheet 2 and 3.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Fixing Today's Date in Cell (2000)

    Changing page setup in one sheet will not automatically carry forward to other sheets. You have to set the header/footer in each sheet separately, or select multiple sheets (click and shift+click or ctrl+click sheet tabs) and set the header and footer in one go.

    If you put a page number in the sheet header or footer, page numbering will start at 1 for each print run, i.e. if you print sheet 1, numbering starts at 1, and if you then print sheet 2, numbering starts at 1 again. But if you select more than one sheet, and click Selected Sheets in the print dialog, numbering will continue; the same holds if you click All Sheets.

  13. #13
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fixing Today's Date in Cell (2000)

    I tried your suggestion of selecting multiple sheets (4). I then ran the macro to insert the footer. I selected all 4 sheets to print. Footer still appears only on the first sheet, but numbered 4.

    Is there some other change I have to make?

    Thanks.

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Fixing Today's Date in Cell (2000)

    If you select all the sheets and goto
    File - page setup - Header/footer(tab) - <custom footer>
    You add the page number by pressing the "page Num" button [the sheet with a "pound sign" (#)].

    Then what Hans said will work.

    Steve

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Fixing Today's Date in Cell (2000)

    My reply was about changing the header and footer interactively. Your code will work on only one sheet, even if multiple sheets are selected, since you modify the page setup for ActiveSheet. You can't modify the page setup of multiple sheets at once in code, you must loop through the sheets.

    Sub SetMultipleFooters()
    Dim wsh As Worksheet
    For Each wsh In ActiveWorkbook.Worksheets
    With wsh.PageSetup
    .LeftFooter = "&Z&F"
    .CenterFooter = Format(Date, "mmmm d, yyyy")
    .RightFooter = "&N"
    End With
    Next wsh
    End Sub

Page 1 of 2 12 LastLast

Posting Permissions

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