Help with Dates!

[Guest]

How do i automatically enter a date that does not change everyday like it
does with the now() function?

 

[Chip Pearson]

You can enter the date with CTRL+<semi-colon> . It will not
update.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


message
news:[email protected]...

 

[Guest]

Thanks Chip for your response. My problems is i have my student file set up
to where it automatically enters a test score date once the test score is
entered. however i have discovered the dates change every time i reopen the
worksheet the following day the formula is:
=IF(P25="","",NOW())
is there a way to have the date automatically enter and not change?

 

[Harald Staff]

Yes, with a simple macro. Rightclick the sheet tab, choose "view code",
paste this in there:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$1" Then Range("C1").Value = Now
End Sub

HTH. Best wishes Harald

 

[Bob Phillips]

Sounds that you need event code


Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("P1100")) Is Nothing Then
Target.Offset(0, 1).Value = Format(Date, "dd mmm,yyyy")
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--

HTH

RP
(remove nothere from the email address if mailing direct)