Date function

[BC]

Hi all,
I want cell b1 to display the current date and time based on cell a1's
entry. I want the date to remain constant and not update unless a1 has been
changed.

I tried this and the date and time changes to current:

=if(a1>=0,now())

Help!

 

[Jacob Skaria]

Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the
right blank portion. Get back to to workbook and try out.


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1:A20")) Is Nothing Then
If Target.Count = 1 Then _
Range("C" & Target.Row) = Format(Now, "mmm dd, yyyy h:mm AMPM;@")
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes

 

[JP]

A bit of programming would work. Put this code in the sheet module
behind the workbook:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("B1").Calculate
End If
End Sub

--JP

 

[Gord Dibben]

To enter a static date/time you can do this manually.

While holding CTRL key hit semi-colon then spacebar then SHIFT + semi-colon.

To do this via VBA code

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value <> "" Then
Excel.Range("B" & n).Value = Format(Now, "mm-dd-yyyy hh:mm:ss")
End If
End If
enditall:
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste to that module.

Alt + q to return to Excel.


Gord Dibben MS Excel MVP

 

[BC]

Jacob,
I tried this, closed, then reopened. File became read-only. Once I opened,
the date changed again.
Here's the formula I'm using.

=if(m198>=0,now())

 

[Jacob Skaria]

To record the timestamp you cannot use a formula..You will have to use a VBA
solution as mentioned in my earlier post.

If this post helps click Yes

 

[BC]

OK,
How about just the date?
--
BGC

To record the timestamp you cannot use a formula..You will have to use a VBA
solution as mentioned in my earlier post.

If this post helps click Yes

 

[BC]

JP,
It didn't work. Closed and reopen and the time updated!

 

[BC]

Gord,
This didn't work either. I held the time, but once I closed and reopened,
the time updated!

 

[Rick Rothstein]

There is no difference between just the date and the date/time... you cannot
"freeze" either one of them using just a formula... it cannot be done
without using VB coding.

 

[JP]

Ugh, you could have mentioned earlier that it needed to stay the same
even if the workbook closed. In that case, you'll probably need to
write more event handlers.

--JP

 

[Gord Dibben]

Do not use a formula in B1.

Let the code enter the date/time for you.

Read ALL my instructions on where to place the code.


Gord