Problem with =NOW() function

[sturnusek]

Hi there,

As its in the title. I have loads of data that needs time stamping, so the operator is clocking the time out/time in. I had loads of problems with just using a simple formula with =NOW(), as it was refreshing every time something got changed. I started using the below to keep the unique values, which it does,

Function myNow() myNow = Now
End Function

but for whatever reason, from time to time the unique values are changing themselves to the current time. I can't disable calculations completely, as it doesn't return me a unique value. I tried to change the settings in the Trust Centre, so the workbook won't be updating itself but had no luck.

Any ideas?

Thanks

 

[joeu2004]

Even though the function is not Volatile, it will get re-evaluated whenever Excel recalculates the entire workbook or worksheet. That can happen when you press alt+f9 and sometimes automatically.

Ostensibly, you might type ctrl+; space ctrl+: Enter in a cell. But that's tedious, especially if individual workers are expected to do that.

Instead, I would create a forms-control button and macro to perform the task.

Creating the forms-control button might be done differently in each version of Excel. In Excel 2010:

1. Click Developer > Insert > Forms Control Button.
2. Click and drag the mouse down and to the right to create the size and shape of the button.
3. In the dialog box, enter a macro name (e.g. clockInOut), and click New.
4. In VBA, enter the following code, for example:

Sub clockInOut()
With Selection
.NumberFormat = "m/d/yyyy h:mm am/pm"
.Value2 = Format(Now, "m/d/yyyy h:mm am/pm")
.EntireColumn.AutoFit
End With
End Sub

5. Edit the button to change the label (e.g. Clock In/Out).

To use: select a cell, then click the button.

Some notes....

Ostensibly, it is sufficient to write .Value2 = Now. But that records time accurate to the second. If that's what you want, fine; after all, with Excel NOW(), you would record time accurate to the 1/100 second. But the use of Format above records time accurate to the minute. I suspect that is preferred.

However, beware of regional differences in formatting the date and time.

 

[sturnusek]

Thank you for your time, it's the simplest solution to my problem that I could think of at the time.

I'm more than happy with the current format, because as you mentioned, it is preferred. But there seems to be some kind of Compile Error when I try to use the code on Office 365, any idea?

Thank you

 

[joeu2004]

there seems to be some kind of Compile Error when I try to use the code on Office 365, any idea?

I'm afraid not. I don't use Office 365. And this is pretty basic VBA code.

First, be sure to copy the text of the macro in my posting and paste it into a VBA editor window.

Second, is there any more explanation other than "compile error"? Or does the line just turn red? If the latter, which line?

The only thought that I have is that you are getting bitten by some regional difference that I alluded to. That is, perhaps your version of Excel does not like "m/d/yyyy" or "h:mm" or "am/pm". But when I experiment with incorrect specifiers in my US version, it does not cause a compile-time error. At most, it might cause a run-time error. But instead, it just causes nonsense results.

Finally, is it possible that there are language differences in the names of the object members?! That is, in your version, the Range members are not called NumberFormat, Value2 and EntireColumn.AutoFit? I doubt it. Just wild speculation; grasping at straws. Anyway, press f2 to open the Object Browser, and look at the Range member names.

I will reach out to other users who use Office 365. In the meantime, please try copy-and-pasting again, and confirm that you still have a problem.


PS.... In hindsight, I realize that the Format specifier can be simply "m/d/yyyy h:mm". But that might change might not fix your problem. The NumberFormat specifier should be as written, with "am/pm".

 

[joeu2004]

Others have confirmed that the macro should work in Office 365 Excel.

However, someone suggest a potential source of the error, even if you copy-and-paste: some or all spaces might become non-breaking spaces (ASCII 160).

To avoid that, copy-and-paste into Notepad first, then copy from Notepad and paste into your VBA editor.

Also, I had second thoughts about the macro. We cannot avoid regional differences when setting range.NumberFormat. (Well, not without some additional effort.) But we can avoid the use of Format. Use the following instead.

Sub clockInOut()
With Selection
.NumberFormat = "m/d/yyyy h:mm am/pm"
.Value2 = Int(Now*1440)/1440
.EntireColumn.AutoFit
End With
End Sub

That also gives us the opportunity to round or round-up to the minute instead of truncating. If that is what you wish, use
WorksheetFunction.Round(Now*1440,0)/1440.

 

[Dave Patton]

My system has Regional Settings of dd/mm/yy
I tried a variety of subs and they all gave correct results.

Excel 2010
	A
6	28-11-2018 4:08 PM
7	28-Nov-2018 4:09 PM
8	28-Nov-2018 4:14 PM
9	28-Nov-2018 16:16
10	28-Nov-18 16:16
11
1c

Sub clockInOut()
With Selection
.NumberFormat = "dd/mmm/yyyy h:mm am/pm"
.Value2 = Format(Now, "dd/mmm/yyyy h:mm am/pm")
.EntireColumn.AutoFit
End With
End Sub

Sub clockInOut2()
With Selection
.Value2 = Now
.NumberFormat = "dd/mmm/yyyy h:mm am/pm"
.EntireColumn.AutoFit
End With
End Sub

Sub clockInOut3()
With Selection
.Value2 = Now
.NumberFormat = "dd/mmm/yyyy h:mm"
End With
End Sub


Sub MyNow_d()
  With ActiveCell
   .FormulaR1C1 = "=NOW()"
   .NumberFormat = "dd/mmm/yy h:mm"
   .Copy
    .PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
  End With
Application.CutCopyMode = False
   
End Sub

 

[sturnusek]

Thank you very much for your answers! I have used your improved code, Joeu2004 and it works fantastic!

Much appreciated.