Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
How can I use the NOW function and keep it from auto updating?
I have created a request for information form in Excel and want Excel to
record the date and time the request is made. I used an IF function to keep the cell empty unless information is place in it and the NOW function to record the date/time if a request is made. The problem is, everytime someone enters a new request for information, every date and time field on the form is updated automatically with the date and time at that time. How can I make this work? I appreciate any assistance to this end. |
#2
|
|||
|
|||
one of the ways to do it is to use a user defined function
function currentdate(ref) if ref = "" then xx="" else xx=now currentdate = xx end function enter the function in a module in your workbook and use = currentdate(B2) for the cell you want to show the date when something is entered in B2 change the cell reference as you want to . "Bear" wrote: I have created a request for information form in Excel and want Excel to record the date and time the request is made. I used an IF function to keep the cell empty unless information is place in it and the NOW function to record the date/time if a request is made. The problem is, everytime someone enters a new request for information, every date and time field on the form is updated automatically with the date and time at that time. How can I make this work? I appreciate any assistance to this end. |
#3
|
|||
|
|||
Bear
You could use event code to enter the date which would remain static. Below are a couple of sets of event code. Use whichever suits you. Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'Col B time will not change if data in Col A is edited On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then n = Target.Row If Excel.Range("A" & n).Value < "" _ And Excel.Range("B" & n).Value = "" Then Excel.Range("B" & n).Value = Format(Now, "hh:mm:ss") End If End If enditall: Application.EnableEvents = True End Sub Private Sub Worksheet_Change(ByVal Target As Range) 'Col B time will change if data in Col A is edited On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then With Target If .Value < "" Then .Offset(0, 1).Value = Format(Now, "hh:mm:ss") End If End With End If ws_exit: Application.EnableEvents = True End Sub To use this code, right-click on the sheet tab and "View Code". Copy/paste the set of code you want to the module. Gord Dibben Excel MVP On Fri, 2 Sep 2005 10:57:04 -0700, "Bear" wrote: I have created a request for information form in Excel and want Excel to record the date and time the request is made. I used an IF function to keep the cell empty unless information is place in it and the NOW function to record the date/time if a request is made. The problem is, everytime someone enters a new request for information, every date and time field on the form is updated automatically with the date and time at that time. How can I make this work? I appreciate any assistance to this end. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|