2007 EXCEL IF and NOW function

[kccheun2]

I am trying to create a time sensitive file. I want to use the IF function to record the time when I input a record.

Under A1 I use: =if(B2, Now(), 0)

Since I need to input records every five minutes or more, I need the A1 to display the time EXACTLY when I put a 1 on B2.

I tried my function and it only gives me the first part of what I need. I need A1 to not correct and refresh itself after the initial input of B1.

Please help .

Thank you

 

[AlphaFrog]

You could do it with an event macro that triggers when you put a value in column B.

• Right-click on the worksheet tab
• Select View Code from the pop-up context menu
• Paste the code below in the VBA edit window

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    [color=darkblue]If[/color] Target.Count = 1 [color=darkblue]Then[/color]
        [color=darkblue]If[/color] Target.Column = 2 And [color=darkblue]Not[/color] IsEmpty(Target) [color=darkblue]Then[/color]   [color=green]'Column B changed[/color]
            Target.Offset(, -1).Value = Now  [color=green]'Timestamp column A[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]

 

[kccheun2]

Thank you very much