Display Current Date & Time in a cell: Everytime I open the workbo

S

sam

Hi All,

Is there a way I can display Current Date & Time in a cell which updates
everytime I open the workbook?

Thanks in Advance
 
F

FSt1

hi
in any cell you wish, enter........
=now()

the cell will update each time the workbook is opened or the sheet is
calculated.
formula is volital meaning it don't stay the same for long.
if you just want the date without time..
=today()

regards
FSt1


regards
FSt1
 
G

Gary''s Student

The following Event macro will put the current date/time in cell A1 of Sheet1:

Private Sub Workbook_Open()
Sheets("Sheet1").Range("A1").Value = Now
End Sub

Because it is workbook code, it is very easy to install and use:

1. right-click the tiny Excel icon just to the left of File on the Menu Bar
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (workbook code), see:

http://www.mvps.org/dmcritchie/excel/event.htm
 
S

sam

Thanks gary, The issue I am having now is: I have a macro that saves the file
and names it with data in Cell A1 which contains the current date and time.
The macro is now giving me an error as I guess there is a ":" in Cell A1. I
am guessing this.

Thanks in advance
 
S

sam

Here is my code to save the file:

Private Sub ExportSave_Click()
Dim FName As String

Application.DisplayAlerts = False

FName = "C:\My Documents\" _
& "Book" & Range("A1").Value "-" & ".xls"

Sheets("Sheet1").Copy
ActiveWorkbook.SaveAs Filename:=FName
ActiveWorkbook.Close

End Sub

Now A1 cell contains the Date and Time stamp. It displays error when I run
this
macro..

Error--- Make sure the specified folder exists.
Make sure the folder that contains the file is not read-only
Make sure the file name does not containg any of the following
characters:
< > ? [ ] : | *
Make sure the file/path name doesnt contain more than 218

Thanks in advance
 
S

sam

Hey Gary, I forgot to mention one thing.. I am going to save the file with
date and time stamp. But I dont want the value to update with current date
and time when I open any previous saved file. Sorry for the confusion before

Hope I made it clear

THanks in advance
 
S

sam

Hey FSt1, I forgot to mention one thing.. I am going to save the file with
date and time stamp(from that cell). But I dont want the date and time value
to update with current date and time when I open any previous saved file.
Sorry for the confusion before

Hope I made it clear

THanks in advance
 
K

ker_01

Adapting Gary's code:

Private Sub Workbook_Open()
If Sheets("Sheet1").Range("A1").Value = "" Then
Sheets("Sheet1").Range("A1").Value = now()
End Sub

You are correct on your other post- if you are trying to save this as your
workbook name, you will probably need to eliminate any special characters
(I'd think the slashes in the date will also be problematic).

Consider the following option; although it isn't as easily readable (what
time is 1609 again?) this might work for you, and still allows you to easily
sort your directories. My PC defaults the hours parameter to military (24
hour) time, so I'd suggest testing it to make sure that it performs as
expected- so you don't have a 4pm file show up as 04xx instead of 16xx and
throw off the order

Private Sub Workbook_Open()

TimeText= Year(Now()) & Format(Month(Now), "00") & Format(Day(Now), "00") &
" " & Format(Hour(Now()), "00") & Format(Minute(Now()), "00")

If Sheets("Sheet1").Range("A1").Value = "" Then
Sheets("Sheet1").Range("A1").Value = TimeText

End Sub
 
F

FSt1

no you're not. the following are forbidden by excel as file nameing
convictions.......
: / \ * [ ]
make a note.
the slashes in the date are forbidden by dos becasue dos would interpet them
as switches and do weird thing or throw up errors.
instead of using the "date in a cell" method, use something like this in
your nameing code....
timestamp = Format(Month(Now), "00") & Format(Day(Now), "00") & _
Format(Year(Now), "0000") & "_" & Format(Hour(Now), "00") _
& ":" & Format(Minute(Now), "00")

or similar.

nice time to bring up..."oh! i forgot to mention......."

FSt1
geez
 
Top