Documenting Static Real Time [Absolute Time] in a Cell

I want a spreadsheet solution to entering the real time into a cell and then somehow making it immune to updates or calculations. Anyone out there have any ideas on this?

Hello,

what do you mean by "making immune"?

Ctrl-Shift ; <that's Ctrl-Shift-semicolon>

enters the current time as an absolute value.

cheers,

Hi SDruley,
I agree with teylyn that using the shortcut keys will make it immune to formulas. You can also do a shortcut for the current date. As a matter of fact there are lots of shortcuts. See:
http://office.microsoft.com/en-us/ex...010073848.aspx

If you want to get fancy you can add Icons onto your ribbon. There is one called Current Time and another called Current Date. Find them in Options, Customize the Ribbon, All Commands.

Thanks teylyn,

How clever, learn something every day.
There is one problem,however, I need this in a formula. Given the following example:

__ A B
1 10 =IF(A1<>"",TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())),"")
2 21 =IF(A2<>"",TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())),"")
3
4

So, once a number pops into cell A3, the current time is placed into B3. The problem is that these times are not, as you say, absolute times. What I am asking is what additional logic can we build into the formula that will populate the cell with an absolute time. I tried the Text function but this did not work.

Steve

Steve,

that will require VBA.

For the record, you don't need

TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))

You can use just

Now()

and format as time only. Same difference.

But to keep the cell from changing, you'll need to engage VBA. See here for an example. Just change the formatting string to your preferred time format.

When you say "pops up" does that mean the number is not entered manually? Then you will need to use the Calculate event. Post an example for specific code.

cheers,

teylyn,

This is sooooo cool...a very nice code solution thanks

Steve

Well, this problem is still not solved. I used code attached to the worksheet that would detect any changes in the worksheet that are in a specified range.
If you open the attached file. please help me understand why nothing seems to trigger the "change"event macro.

Hi Steve,

You have the line

Please Login or Register  to view this content.

Didn't you mean to make it

Please Login or Register  to view this content.

Hi Steve,

can you describe in words what you want to do?

A Workshee_Change event will only fire for the worksheet that it is stored in. If you have a change event in sheet "Mike's Interface" then it will only do something if data is manually entered in that sheet.

I'm afraid I'm not quite clear on what you want changed based on what trigger.

Please explain.

Now I understand...
I mistakenly thought that changes in the target worksheet (brought about by formulas referring to other worksheets) would trigger the change event. NOT

Also I have moved away from this topic because I have found a more efficient Programmatic solution.

Thanks everyone for your precious time

Steve