Return cuurent time when another cell is updated

Catalin.B · #1 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 · #2 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 · #3 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 · #4 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

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