locked
Use of "NOW()" Function in Professional Plus Excel 2007 RRS feed

  • Question

  • I am using Excel Professional Plus 2007 to enter an Accession Number, by reading a bar code label, into one column in Excel.  In the adjacent column B, I have entered the formula:  =IF(A2="","",NOW()).  When I read a bar code into cell A2, the correct date/time (e.g., 06/23/2010 12:14) is entered into cell B2; however, when I read a bar code into a cell after the time has changed (e.g., 12:15), Excel enters the correct date/time in the adjacent cell, but it updates all previous cells to the most recent time.  I want the time portion of the date/time entered via the "NOW()" function to remain fixed at the time the bar code was read and not update any previous cells.

    Can Excel do this, or is the NOW() function such that you have only 1 option:  it enters the current date/time and for any other cells containing this function it always updates to the new time?

    Thanks,

     

    FHW

    Thursday, June 24, 2010 5:20 PM

Answers

  • Here is some event code that will do what you want: copy the code, right-click the sheet tab, select "view code" and paste the code in the window that appears.
     
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myCell As Range
    If Target.Column <> 1 Then Exit Sub
    If Target.Cells.Count <> 1 Then Exit Sub
    Application.EnableEvents = False
    With Target(1, 2)
        .Value = Now()
        .NumberFormat = "mm/dd/yy hh:mm"
    End With
    Application.EnableEvents = True
    End Sub


    HTH,
    Bernie
    MS Excel MVP
     
     

    I am using Excel Professional Plus 2007 to enter an Accession Number, by reading a bar code label, into one column in Excel.  In the adjacent column B, I have entered the formula:  =IF(A2="","",NOW()).  When I read a bar code into cell A2, the correct date/time (e.g., 06/23/2010 12:14) is entered into cell B2; however, when I read a bar code into a cell after the time has changed (e.g., 12:15), Excel enters the correct date/time in the adjacent cell, but it updates all previous cells to the most recent time.  I want the time portion of the date/time entered via the "NOW()" function to remain fixed at the time the bar code was read and not update any previous cells.

    Can Excel do this, or is the NOW() function such that you have only 1 option:  it enters the current date/time and for any other cells containing this function it always updates to the new time?

    Thanks,

     

    FHW

    • Marked as answer by Jennifer Zhan Monday, June 28, 2010 1:09 AM
    Friday, June 25, 2010 4:50 PM
  • Hi,


    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim Isect As Range
        Set Isect = Application.Intersect(Target, [A:A])
        If Not Isect Is Nothing Then
            Target.Offset(0, 1) = Now
        End If
    End Sub

    You can shorten the range if you know it will only go form A1:A100 put that in the [A1:A100]


    If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire
    • Marked as answer by Jennifer Zhan Monday, June 28, 2010 1:09 AM
    Friday, June 25, 2010 11:59 PM

All replies

  • That's how now() is supposed to work. It will always show the current time, whenever the worksheet is calculated. For what you want, you need some other approach.
    Thursday, June 24, 2010 5:48 PM
  • Just an idea: you could have the worksheet change event watching the column where your bar codes are stored; whenever a change happens, you find which cell changed and then you write, to the cell at the right, the current time at that time; once written, it will not change anymore. You'll need a macro for this.
    Thursday, June 24, 2010 5:51 PM
  • Here is some event code that will do what you want: copy the code, right-click the sheet tab, select "view code" and paste the code in the window that appears.
     
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myCell As Range
    If Target.Column <> 1 Then Exit Sub
    If Target.Cells.Count <> 1 Then Exit Sub
    Application.EnableEvents = False
    With Target(1, 2)
        .Value = Now()
        .NumberFormat = "mm/dd/yy hh:mm"
    End With
    Application.EnableEvents = True
    End Sub


    HTH,
    Bernie
    MS Excel MVP
     
     

    I am using Excel Professional Plus 2007 to enter an Accession Number, by reading a bar code label, into one column in Excel.  In the adjacent column B, I have entered the formula:  =IF(A2="","",NOW()).  When I read a bar code into cell A2, the correct date/time (e.g., 06/23/2010 12:14) is entered into cell B2; however, when I read a bar code into a cell after the time has changed (e.g., 12:15), Excel enters the correct date/time in the adjacent cell, but it updates all previous cells to the most recent time.  I want the time portion of the date/time entered via the "NOW()" function to remain fixed at the time the bar code was read and not update any previous cells.

    Can Excel do this, or is the NOW() function such that you have only 1 option:  it enters the current date/time and for any other cells containing this function it always updates to the new time?

    Thanks,

     

    FHW

    • Marked as answer by Jennifer Zhan Monday, June 28, 2010 1:09 AM
    Friday, June 25, 2010 4:50 PM
  • Hi,


    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim Isect As Range
        Set Isect = Application.Intersect(Target, [A:A])
        If Not Isect Is Nothing Then
            Target.Offset(0, 1) = Now
        End If
    End Sub

    You can shorten the range if you know it will only go form A1:A100 put that in the [A1:A100]


    If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire
    • Marked as answer by Jennifer Zhan Monday, June 28, 2010 1:09 AM
    Friday, June 25, 2010 11:59 PM