Problem with the NOW() formula

Microsoft Excel 2000/visual basic for ap...
July 27, 2009 at 09:13:22
Specs: Windows XP
Hello. I am trying to make a spreadsheet which enters the current date as soon as you enter anything in the adjacent cell. The formula I am using is =IF(F7="";"Not Tested Yet";NOW()). (I'm using openoffice which uses semicolons rather than commas). The formula calculates the date correctly when text is entered, but If I enter text in a separate cell (F8), The dates and times in G7 and G8 both turn to the current time. I'd like the formula to get a date, and only change if the text in the adjacent cell changes.

See More: Problem with the NOW() formula

Report •

July 27, 2009 at 10:49:57
As you have seen, the problem with using NOW() is that everytime the sheet recalculates, it updates every NOW() with the current "now".

You need to lock in "now" via a VBA change macro.

For example, with this code, whenever you enter something in Column F, the cell in the same row of Column E will be set to the current date and time. The date and time will not change unless you change the value in that same cell in Column F.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 6 Then Target.Offset(0, -1) = Now()
End Sub

EDIT: This assumes that "standard" Excel VBA code runs in OOo 3. I was doing some browsing just now, and I ran across a post about needing to use something called the Listener to monitor changes to a cell in OOo 3. I don't use OOo 3, so I can't speak to that.

Report •
Related Solutions

Ask Question