Automatically enter today's date as a static entry

[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.

 

[Guest]

David,
Are you familiar with Visual Basic? If so, try this code in ThisWorkbook
section.

Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Sheet1").Select
Range("A1").Select
ActiveCell.Value = Now
End Sub

Actually, this returns an entire Date/Time stamp. If you wanted just the
date you could use DateSerial(Year(Now),Month(Now),Day(Now)) where "Now" is
above.

Dave

 

[Peter T]

Hi David,

in the ThisWorkbook module -

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

'if don't want date if no changes since last save
If ThisWorkbook.Saved = True Then Exit Sub

On Error Resume Next
Application.EnableEvents = False
Worksheets("Sheet1").Range("A1") = Now
Worksheets("Sheet1").Range("A2") = Date
Application.EnableEvents = True

End Sub

Handy keyboard short cuts
Ctrl ;
semicolon - date
with shift or colon - time

Regards,
Peter T

PS FYI, my newsreader removed an attachment from your post it decided was
unsafe.

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.

 

[cscorp]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel A
Boolean)

ThisWorkbook.Sheets("Sheet1").Range("A1").Value = Now

End Sub


1. Replace "Sheet1" with the name of the sheet where you want to inser
the date.

2. Replace "A1" with the address of the cell where you want to ente
the date.

The Workbook_beforeSave event is an event of "Thisworkbook" object.

Juan Carlo

 

[David]

Thanks Peter, yours seems to have worked best of all the solutions I
received. The others may have worked just as well if I knew more about VB.

Can I take care of formatting so it ends up as March 12, 2005 in your code
vs. in the spreadsheet?

Thanks again for your help.

 

[Ron de Bruin]

Use this then

Worksheets("Sheet1").Range("A1").Value = Format(Date, "mmm dd yyyy")

 

[Peter T]

Can I take care of formatting so it ends up as March 12, 2005 in your code

vs. in the spreadsheet?

Assuming the address of the cell you saving the date to is hardcoded,
manually format the cell to display the date as required, no need to do it
each time in the Save event (providing user hasn't changed it).

Or if needs, with code

myCell.NumberFormat = "mmmm dd, yyyy"

Regards,
Peter T