Customised Now() still automatically updating

[Jessmeach]

Hey guys,
I created a spreadsheet in Excel to track workers signing in and out. I had a customised Now() code as part of the formula to prevent the timestamp at each entry from updating, which works whilst I’m active in the workbook however as soon as the file is saved and then re-opened, all the date and time cells logged automatically change to the updated date and time. How do I stop this from happening??

Below is the code I use and the formula.

Function myNow()
myNow = Now
End function

Formula = IF(AND(A9<>””,A9<>0),myNow(),””)

Any help would be greatly appreciated !

 

[alansidman]

If you only need the Date then

Function Mnow()
MyNow = Date
End Function

If you only need the Time then

Function MTime
MyTime = Time
End Function

 

[Jessmeach]

I need both the date and time in the cell and for them not to update

 

[pjmorris]

In truth, as you've discovered, the function will automatically update when the complete sheet is recalculated. Your process only works because (I think this is the right wording) user defined functions are non-volatile and only recalculate when excel believes cells on which the function is dependent have changed. Your function isn't dependent and so isn't recalculated, hence the apparent stability. Press F9 and it will almost certainly update, which is not what you want. I don't know of a function that would save the value to the cell - you would need to copy and paste (perhaps as part of a workbook_beforeclose process) the relevant values.

Hope that helps

 

[Peter_SSs]

Welcome to the MrExcel board!

Instead of a function, you might be better to use a Worksheet_Change event code along the lines of this: Inputting fixed time

Post back with details if you need specific modifications that you are unable to achieve yourself.

 

[Jessmeach]

Welcome to the MrExcel board!

Instead of a function, you might be better to use a Worksheet_Change event code along the lines of this: Inputting fixed time

Post back with details if you need specific modifications that you are unable to achieve yourself.

Hi Peter, this sounds great! How would I write the code if I needed the date and time to be in column H, once data is imputed into column A?
Thanks for your help!

 

[Peter_SSs]

How would I write the code if I needed the date and time to be in column H, once data is imputed into column A?

1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range, c As Range
  
  Set rng = Intersect(Target, Columns("A"))
  If Not rng Is Nothing Then
    Application.EnableEvents = False
    For Each c In rng
    With Cells(c.Row, "H")
      .Value = Now
      .NumberFormat = "dd mmm yy hh:mm"
    End With
    Next c
    Application.EnableEvents = True
  End If
End Sub