today's date in excel

[David]

I have a need to automatically save the current date to a cell in an excel spreadsheet every time the sheet is saved.

Today() always returns the current date, so once I open the sheet on a new day I can no longer see the date the information was last updated, i.e. saved.

Any help would be appreciated.

 

[R.VENKATARAMAN]

after entering today() did you try copying this and pastespecial(values) on
the same cell
==============================
I have a need to automatically save the current date to a cell in an excel
spreadsheet every time the sheet is saved.

Today() always returns the current date, so once I open the sheet on a new
day I can no longer see the date the information was last updated, i.e.
saved.

Any help would be appreciated.

 

[Dave Peterson]

First, I find it easier to read messages when they're posted in plain
text--instead of HTML. Lots of people like plain text in this text only
newsgroup.

But you could use a workbook event that changes the date each time you save the
workbook.

Rightclick on the excel icon (to the left of the File option on the worksheet
menu bar).

Select view code and paste this in the code window:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.EnableEvents = False
With Me.Worksheets("sheet1").Range("a1")
.Value = Date
.NumberFormat = "mm/dd/yyyy"
End With
Application.EnableEvents = True
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm