inserting current time absolute value

  • Thread starter Thread starter happyPotter
  • Start date Start date
H

happyPotter

hey everyone

I'm trying to create a formula that when something is entered in one
cell the current time is stamped in another cell. So far here's what
I came up in my C4 cell

=IF(A4<>"", NOW()-TODAY(), "")

When something is entered in cell A4, the current time enters in at
cell C4. I copied the formula by using fill down, but when I enter in
something in the next cell of A5, cell C5 and C4 gets updated with the
same current time, when i enter the something in A6, cells C6, C5, C4
gets updated with the same time and ETC, ETC

How would I use absolute value in my formula to stop the previous cells
from updating to the latest cell?
 
Hi - Try this:

Click Alt-F11 to bring up the Visual Basic Editor (VBE)

Look down the left-hand side and find the sheet in your workbook with the
cells in that you are interested in.

Right click on that sheet and select View Code

When that brings up a new pane in the VBE, insert the following:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Range("A4") <> "" And Range("C4").Value < 0.001 Then Range("C4") =
Format(Now, "hh:mm:ss")

End Sub

Geoff
 
If the other answers weren't what you were looking for, my guess is that
you want to enter a constant instead of a formula. The formula will
update whenever a calculation is done, or the workbook is opened,
a constant will always remain the same.

To type in the current date use Ctrl+; (semi-colon)
To type in the current time use Ctrl+Shift+: (colon)

to include both use Ctrl+: space Ctrl+Shift+:

If you want to simply timestamp column A when something is
entered in Column B, you can use an Event macro
Worksheet Events and Workbook Events
http://www.mvps.org/dmcritchie/excel/event.htm#autodate



GB said:
Hi - Try this:

Click Alt-F11 to bring up the Visual Basic Editor (VBE)

Look down the left-hand side and find the sheet in your workbook with the
cells in that you are interested in.

Right click on that sheet and select View Code

When that brings up a new pane in the VBE, insert the following:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Range("A4") <> "" And Range("C4").Value < 0.001 Then Range("C4") =
Format(Now, "hh:mm:ss")

End Sub

Geoff
Click to expand...
 
Another approach you might to try is a technique I first saw described by
Stephen Dunn, which makes use of circular references.

Here are step by step instructions.

1. Format column C as time
2. Go to menu Tools>Options
3. On the View tab, uncheck the zero values checkbox, which is in the
middle column of the bottom section (this is to suppress all the zero time
values)
4. On the Calculation tab, check the Iteration checkbox (this will stop the
circular references message)
5. In cell C4, put this formula
=IF((A4="")+(C4>0),C4,NOW()-TODAY())
You should start with a zero.
6. Copy this formula down to C6 and on, as far as you are ever likely to
need to go

Then whenever you put anything in A4, A5, etc., C4, etc will get the current
time, but will not change when a new row is updated.
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

GB said:
Hi - Try this:

Click Alt-F11 to bring up the Visual Basic Editor (VBE)

Look down the left-hand side and find the sheet in your workbook with the
cells in that you are interested in.

Right click on that sheet and select View Code

When that brings up a new pane in the VBE, insert the following:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Range("A4") <> "" And Range("C4").Value < 0.001 Then Range("C4") =
Format(Now, "hh:mm:ss")

End Sub

Geoff
Click to expand...
 
Back
Top