Customised Now() still automatically updating

Jessmeach

New Member
Joined
Oct 15, 2021
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
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 !
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,762
Office Version
  1. 365
Platform
  1. Windows
If you only need the Date then

VBA Code:
Function Mnow()
MyNow = Date
End Function

If you only need the Time then

VBA Code:
Function MTime
MyTime = Time
End Function
 

Jessmeach

New Member
Joined
Oct 15, 2021
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
I need both the date and time in the cell and for them not to update
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
2,036
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,263
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Oct 15, 2021
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,263
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,824
Messages
5,766,657
Members
425,367
Latest member
Boboka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top