date formula not right

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob Bob - 25 Nov 2007 01:39 GMT

=IF(OR(ISBLANK(A1),A1=0),"",NOW())
I am using this formula in B1 to record the date when cell A1 has been
updated. Problem is the date is always updating to the current date. How can
I change it so it show when the cell was updated?

Thanks

Reply to this Message

Alan - 25 Nov 2007 02:13 GMT

=NOW() or =TODAY() etc will always update everytime the worksheet
calculates.
There is as far as I know no formula which will return a fixed date, Ctrl
and : pressed together will give the date which will not change.You can use
VB code to hard copy the date when you update A1, or you can copy > paste
special > values to lose the formula and leave only the result in A1.
If you want to use VB code there are lots of examples on these groups which
can be Googled.
Regards,
Alan.

> =IF(OR(ISBLANK(A1),A1=0),"",NOW())
> I am using this formula in B1 to record the date when cell A1 has been
[quoted text clipped - 3 lines]
>
> Thanks

Reply to this Message

Bill Kuunders - 25 Nov 2007 02:23 GMT

enter this in the worksheet code under "change"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Application.EnableEvents = False
Target.Offset.Offset(0, 1) = Now()
Application.EnableEvents = True
End If
End Sub

As described in OZgrid http://www.ozgrid.com/forum/showthread.php?t=37642

Regards

Signature

Greetings from New Zealand

> =IF(OR(ISBLANK(A1),A1=0),"",NOW())
> I am using this formula in B1 to record the date when cell A1 has been
[quoted text clipped - 3 lines]
>
> Thanks

Reply to this Message

Alan - 28 Jan 2008 02:39 GMT

Test

> =IF(OR(ISBLANK(A1),A1=0),"",NOW())
> I am using this formula in B1 to record the date when cell A1 has been
[quoted text clipped - 3 lines]
>
> Thanks

Reply to this Message