How do I stop the Now() function from updating everytime I open the file?

Question

I need it to automatically grab the value of current time at a particular moment when the user does a specific action (like pressing a button maybe). The thing is, I need it to keep that value of time and not continue to update is as it generates new registrys in the rows below it.

What I'm trying to get is an Excel workbook that logs and keeps track of the time that my employees get to work, reason for which I dont want the user inputing the value of time but rather just pressing a button in a user form and the computer automatically generating and storing that data for later use by the admin.

The problem is that the Now() function always updates to the current time.

Help is appreciated!

If your objective is to keep track of the times that your employees do things, see Show timestamp when cell is changed, How to make a VBA procedure run in Excel when a cell is changed, or Create timestamp formula for Excel. Or simply search Excel/VBA documentation for Worksheet_Change. — Scott - Слава Україні, May 30 '15 at 21:24
Will the user need to log off as well? If you're this picky with times, do they need to log on and off for lunch breaks, getting a drink, popping to the toilet etc... Is this document one per person? — Dave, Apr 01 '18 at 12:44

score 1 · Answer 1 · answered Mar 21 '20 at 08:27

1

Make File->Option->Formulas->Calculation options as Manual

answered Mar 21 '20 at 08:27

Krishna Punekar

11

score 1 · Answer 2 · answered May 30 '15 at 18:10

That is the function of now(), to give the current time.

What you need is some simple VBA to return the current date/time when a button is pressed.

You don't say what version of Excel. For 2010/2013 you will need to turn on the Developer tab in the Ribbon and ensure that you set Excel to allow running macro's.

From the developer tab, inset a "Button (Form Control)". Give it a new Macro to work with.

Put in the following code:

Sub Button1_Click()
    Application.ActiveCell.Value = Now()
End Sub

It inserts the current date/time as a fixed value in the currently selected cell.

How do I stop the Now() function from updating everytime I open the file?

2 Answers2