Is there an alternative for =NOW()

[Ido Poelman]

We have a measuring instrument ( a caliper) which puts the measured dimensions automatically in an Excel file.
We want to register the time when the the cell in the Excel file is filled with the information from the caliper.

If we use =IF(R1C1>0;NOW()) for instance, it does not work. If you put new information in an other cell the time changes in the cell with the formula =IF(R1C1>0;NOW()).

Is there an alternative for NOW()?

Ido Poelman

[joeu2004]

We have a measuring instrument (a caliper) which puts the measured dimensions automatically in an Excel file. We want to register the time when the the cell in the Excel file is filled with the information from the caliper.

If we use =IF(R1C1>0;NOW()) for instance, it does not work. If you put new information in an other cell the time changes in the cell with the formula =IF(R1C1>0;NOW()).

Is there an alternative for NOW()?

First, do you want to record "the time" [sic] or the date and time? Now() returns the latter. MOD(NOW(),1) returns just time of day.

In any case, perhaps the following changes will work for you. Change the formula to:

=IF(R1C1>0;myNow())

and add the following macro into a "regular" VBA module:

Function myNow() As Double
myNow = Now
End Function

Change Now to Time if you just want "the time", not date and time.

Finally, the formula seems odd. It returns FALSE if R1C1<=0 is true. Perhaps it should be:

=IF(R1C1>0;myNow();"")

[alansidman]

@joeu2004
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

[mehmetcik]

you use a simple macro to fix your value.

right click on the sheet name at the bottom of excel and select view code.

Paste this there.

Please Login or Register  to view this content.

Each time you change A1 the present date and time will be entered into B1.

[alansidman]

@Ido Poelman

If you enter your data in this manner

CTL + ;

this will give you the current date hard coded. You can then use that to compare to Now() or Today() in your formula. Using either of those functions to enter data will change your data as you update the worksheet.

[Ido Poelman]

This very helpful mr. or mrs mehmetcik. It works. What do I have to do when I want this for cell B2 as well?

[mehmetcik]

Please Login or Register  to view this content.

[Ido Poelman]

Dear mr. or mrs mehmetcik. How simple can it be. Thank you verry much for you help. Ido Poelman

[mehmetcik]

Its is my pleasure.