Date/Time Macro

M

Michael Link

I guess this is part 2 of a query I started below. I need a macro that will
enter both the current date and time into the active cell whenever it is run.
Since this is for a workbook shared across a network, the information needs
to remain unchanged in the cell even if the macro is run for another active
cell by someone else on the network.

Help! I've recorded or written very few macros, and unfortunately I need to
have this one running as soon as possible. Thanks in advance for your help,
and thanks to the folks who offered assistance to a related date/time query
earlier today.
 
B

Bob Phillips

Sub DateAndTime()
With ActiveCell
.Value = Now
.NumberFormat = "dd mm yyyy hh:mm:ss"
End With
End Sub


--

HTH

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


I guess this is part 2 of a query I started below. I need a macro that will
enter both the current date and time into the active cell whenever it is
run.
Since this is for a workbook shared across a network, the information needs
to remain unchanged in the cell even if the macro is run for another active
cell by someone else on the network.

Help! I've recorded or written very few macros, and unfortunately I need to
have this one running as soon as possible. Thanks in advance for your help,
and thanks to the folks who offered assistance to a related date/time query
earlier today.
 
G

Guest

Very cool--you know, even though it's such a small routine, I could never
have come up with it. Thanks a lot! I wonder--is it possible to have the
macro return the time so that it isn't in military time, but uses an "AM" or
"PM" indicator instead? (And I can't tell--is there one space bar space
between the date and time so that it conforms with Excel date/time format
and can be used for calculations?) The folks who will use this could of
course do the calculation themselves, but I want the info to be as quickly
absorbed as possible.

Again--very cool. Thank you so much!
 
G

Guest

Bob Phillips will probably answer your question better but
you could always use the =NOW() function or =DATE() and
=TIME() functions and just write your macro to
recalculate (F9)
Go to the cell(s) with the fomula(e) and copy
And Go to a destination cell and paste special values.
You can formet the destination cells any way you want
 
G

Guest

That last one I sent doesn't work, but you can put =NOW()
in one cell, recalculate, copy, paste values in two cells,
with one formatted as date, and the other formatted as
time.
If you're just pasting values, the destination cells will
hold their formatting.
 
B

Bob Phillips

Michael,

The format is just that, a format, so it can be changed in many ways,
nothing to do with keeping it as a number, but is simply a matter of how it
looks. But, it is still a number, so it can be used for calculations as you
need.

The new macro would just be

Sub DateAndTime()
With ActiveCell
.Value = Now
.NumberFormat = "dd mmm yyyy hh:mm AM/PM"
End With
End Sub

Note that I added an m in the month format so it shows as Feb not 02.

--

HTH

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


Very cool--you know, even though it's such a small routine, I could never
have come up with it. Thanks a lot! I wonder--is it possible to have the
macro return the time so that it isn't in military time, but uses an "AM" or
"PM" indicator instead? (And I can't tell--is there one space bar space
between the date and time so that it conforms with Excel date/time format
and can be used for calculations?) The folks who will use this could of
course do the calculation themselves, but I want the info to be as quickly
absorbed as possible.

Again--very cool. Thank you so much!
 
Top