Return cuurent time when another cell is updated

zks · #1 01-20-2012, 09:17 AM

I have a unsolved issue with this problem:

There is a table with a lot of data, and some of them we change couple of times. I want to have a cell(s) where I can get the time (hh:mm:ss) when that cell was filled with data (this cell is empty). (Exp: A1 is empty. At 07:25:33 I entered a data in A1. I want to have this time (07:25:33) at B1 cell. Before I print this report (last update Exp: 15:20:25) i will have a cell (C1) where I will use =Now() and I will got 15:20:25. My point is to have, at D1, how much time I was working on this sheet D1:=C1-B1!

I need a help with this!

Thanks

Pecoflyer · #2 01-20-2012, 10:23 AM

Cross post
http://www.excelforum.com/excel-gene...s-updated.html

zks · #3 01-20-2012, 10:31 AM

SOLVED....

B1=IF(A1<>"",IF(B1="",NOW(),B1),"")

Thanks friends, .. from all Excel forums on internet!

Catalin.B · #4 01-20-2012, 11:25 AM

Glad you found the solution, but i really believe it's wrong...
For 2 reasons: you create a circular reference by refering in the formula to the cell where the formula is placed, and second reason is that if you eliminate the first problem, and you have a value in A1, your formula will put a formula in B1: Now(), formula that will permanently refresh, and you said you need the time when data is entered in A1, not the Now() value..
To insert a permanent date and time value in a cell, can be done with a macro, targeted to A column

zks · #5 01-20-2012, 11:44 AM

That is correct, but after I'm done and I print the report I dont need this sheet anymore ... BUT for the future use of the sheet, I should probably consider your solution!

I enabled the circular formula with:
tools > options > calculation tab and click the iteration box

Thanks for the all Help!

Catalin.B · #6 01-20-2012, 12:07 PM

This is the way to get a good result

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RNG As Range, cell
Set RNG = ActiveSheet.Range("A2:A10")
If Not Intersect(Target, RNG) Is Nothing Then
For Each cell In RNG
If Len(cell) > 0 And Not IsEmpty(cell) Then
cell.Offset(0, 1) = Now()
End If
Next cell
End If
End Sub

Catalin.B · #7 01-22-2012, 10:24 PM

Made some corrections to work properly:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RNG As Range, cell
Set RNG = ActiveSheet.Range("A2:A10")
If Not Intersect(Target, RNG) Is Nothing Then
For Each cell In RNG
If Len(cell) > 0 And IsEmpty(cell.Offset(0, 1)) Then
cell.Offset(0, 1) = Now()
End If
Next cell
End If
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Cell formatting for time - Excel 2010	PomDave	Excel	5	09-05-2011 10:49 PM
Item Already updated	simon@resultsoftware.co.u	Outlook	0	07-25-2011 04:30 AM
How can references be updated automatically?	ezthelm	Word	2	05-30-2011 03:45 AM
Add a period of time to a referenced "Time" cell?	derelyth	Excel	4	03-27-2011 04:31 PM
Outlook 2002 duplicating updated calendar events	RMTony	Outlook	0	12-29-2010 11:48 AM