Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date functions that don't automatically update (Excel 2000 SR-3 (?))

    I know that NOW () and TODAY () and DATE () will insert the current date (and time in some cases). But don't those automatically update with current information? I'm looking for a formula that will insert the current date when first used, then NOT update again in the future. A possible solution would be to just TYPE IT MANUALLY, but that would be to easy (and too manual). <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

  2. #2
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date functions that don't automatically update (Excel 2000 SR-3 (?))

    Hi, when do you want the date field to automatically update again? Is it every time the file is opened? or saved?

    would you want to have it update by using a button to click it?
    Thanks,

    pmatz

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date functions that don't automatically update (Excel 2000 SR-3 (?))

    You do know you can hit control-; to enter the current date and control-: to get the current time?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Date functions that don't automatically update (Excel 2000 SR-3 (?))

    I don't think you should use a formula here, unless you want to enter a formula first and then convert it to a value, but that seems to be more trouble than it's worth.

    Ctrl+semicolon inserts the current date and Ctrl+Shift+semicolon inserts the current time, both as a fixed value. Is that still too manual?

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date functions that don't automatically update (Excel 2000 SR-3 (?))

    I knew about the CTRL+; keyboard combination. We are trying to automate a process - there's a daily activity log that we have to fill out by hand, and we are trying to come up with a way to do it online and insert the current date automatically. That is easy enough with the DATE() or TODAY () functions. BUT, once that date is inserted, it needs to remain the originating date and not autoupdate.

    I suppose we could do it using keyboard shortcuts since there doesn't appear to be a formula or function that will NOT automatically update. I was hoping to have this date field in some header info that the user never had to touch, thus the problem with having to do it manually.

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

    Re: Date functions that don't automatically update (Excel 2000 SR-3 (?))

    If macros are acceptable, you could write a macro that sets the date and/or time in the page header or footer, but you haven't answered the questions by M

  7. #7
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date functions that don't automatically update (Excel 2000 SR-3 (?))

    Sorry - I read M(symbol I don't know)tz's question as "when do you want the field to automatically update" and the answer is "I don't." I just want the date inserted when the form is first opened. I think that your macro idea is a good one.

    Part of my problem is that I'm trying to answer a hypothetical question for a form I haven't seen. The person creating the form asked this, but has yet to send me their form. It might be that I'll have a better idea of how to handle the issue when they actually send me a copy. <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

    Thanks for all of your help. - Sat.

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

    Re: Date functions that don't automatically update (Excel 2000 SR-3 (?))

    Say that you want the date and time in the page header area of Sheet1. You could have code in the Workbook_Open event to check of the date has already been set there; if so, it is left unchanged, otherwise the date/time is set.

    Private Sub Workbook_Open()
    If Worksheets("Sheet1").PageSetup.CenterHeader = "" Then
    Worksheets("Sheet1").PageSetup.CenterHeader = Now
    End If
    End Sub

    This code should *not* go into a standard module, but into the ThisWorkbook module:
    - Activate the Visual Basic Editor (Alt+F11)
    - Activate the Project Explorer (Ctrl+R)
    - If necessary, expand the relevant workbook until you see ThisWorkbook.
    - Double click ThisWorkbook.
    - Type or copy the above procedure. Modify as needed.

    Users need to set macro security to Medium (preferred) or Low, not to High.

Posting Permissions

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