Hi,

It gave a circular reference because you put it in either A6 or B6, you
never got around to telling us which cell you wanted the date/time in. No
matter how long you play with this unless you use the circular reference
method given to you by Gord you won't achieve what you want using a formula.
If you want to use code then tell us the range of cells to look at and where
to put the date & time.

Mike

"Mark A. Sam" wrote in message
...
Mike,

=IF(AND(A6="",B6<""),"",NOW()) gave me circular reference problem. The
subroutine you gave me also refreshed the other cells. Maybe becuase the
worksheet automatically refreshes whenever a cell is updated?



"Mike H" wrote in message
...
Hi,

Excel won't like that because the correct syntax is

=IF(AND(A6="",B6<""),"",NOW())

But you will still have the same problem of NOW() updating every time the
worksheet calculates as soon as those 2 conditions evaluate as TRUE

Mike
"Mark A. Sam" wrote in message
...
This may be awkward for the client and before I do this, I'd like to ask
another question.

I tried modifying the formula using the And operator to test the value
of the cell containing the formula,

=IF(A6="" and B6<"","",NOW())

Excel didn't like it. Am I referencing the cells incorrectly or is this
just an illegal operation?

God Bless,

Mark

"Mike H" wrote in message
...
Hi,

You could do this. Right click your sheet tab, view code and paste this
in. It puts the static date/time in A3 whe A2 is updated

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$2" Then
Application.EnableEvents = False
Range("A6").Value = Format(Now, "dd/mm/yyyy hh:mm")
Application.EnableEvents = True
End If
End Sub


Mike
"Mark A. Sam" wrote in message
...
Hello,

I'm not too proficient with Excel so hopefully this can be done. My
client is using this formula to update the date and time in a
particular cell:

=IF(A2="","",NOW())

He wants it to update only when A2 is updated, but it updates when any
cell is updated. Does this require a code and if so how to implement
it.

Thank you for any help and God Bless,

Mark A. Sam