Setting today's date automatically in Excel.

[Ezlpo]

I have used the NOW() function to automatically set in today's date when I am
entering data in Excel. The problem is the spreadsheet is an ongoing report
so when the user is done inputting data they save it and close it. The next
day a user will open it and save data and close it.

The problem is that when I use the NOW () function, the dates of the
previous days switch to the current date. I understand why that happens, but
I don't want the dates to change for the previous data. Data is not entered
every day, but almost (even on weekends).

I saw the TODAY () function, but I fear it will have the same results.

What is the formula I should use?

 

[JulieD]

Hi

all formulas recalculate - so you basically have two options - one is to use
code to change the date to a static value, the other is to enter the date
each day - the short cut for this is control & semi-colon (

if you're interested in a code solution - what cells is your date function
in?

Cheers
JulieD

 

[Peo Sjoblom]

http://www.mcgimpsey.com/excel/timestamp.html

 

[Ezlpo]

C column is where the dates reside. A code solution would be great as there
are enough entries that it would actually save time to not have to enter in
the date on each cell. Even the "filler" option is tedious. I am looking to
automate as much as possible so the user doesn't need to do much so they
can't make too many mistakes.

 

[JulieD]

Okay

so i'll need to know what "trigger" you want for the date to be put in
column C of the row you're working in - do you always enter data in column A
of each row or???

Cheers
JulieD

 

[Ezlpo]

Yes, Column A will always have a Name in it and C will always have date in it.

Thanx.

 

[JulieD]

Hi

to use this code, right mouse click on the sheet tab of the sheet you're
dealing with and choose view code,
copy and paste the code on the right hand side screen

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 And Not IsNull(Target.Value) Then
Target.Offset(0, 2).Value = Date
End If

End Sub

---
Note, everytime a value is entered or changed in column A the date will be
entered in column C

Cheers
JulieD