Time in Excel '97

M

Matt J.

Is there a way in Excel '97 to get it to insert the exact time? What I mean
is that if I push Ctrl+Shift+:, the time inserts only hours, minutes and
AM/PM, but no seconds. Actually, I just tried this in Excel XP and it does
the same thing. Any ideas? Would really appreciate it.
 
N

Nick Hodge

Matt

You could put this code in your personal.xls workbook and assign it to a
keyboard shortcut.

Sub InsertTimeWithSecs()
ActiveCell.Value = Format(Now(), "hh:mm:ss")
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
 
M

Matt J.

Nick,

Thanks for the response. This looks like it could work (unfortunately, I'm
not at work right now to check it out on '97), but I have a followup
question, as well as a new one.

You said it's possible to assign a keyboard shortcut to this...what would
that line of code look like? (new to VBA, or whatever this is...just now
getting to be more than a casual user, but not that advanced yet)

Also, if I format the current cell to hh:mm:ss AM/PM and press Ctrl+Shift+:,
it comes up as 08:54:00 AM, no matter what the current second is. BUT, if I
format the cell the same way and enter the formula =NOW(), it comes up as
08:55:46 AM. Is there a way to make this value just stay as is? I remember
reading further down in this group that once a cell contained data that the
formula would be erased and the data would remain in place, but I can't find
it for the life of me.

Thanks
M. Johnson
 
J

JulieD

Hi Matt

to assign a keyboard shortcut to Nick's code, once you've copied & pasted
his code into a module sheet in your personal macro workbook (to do this
choose window / unhide, select personal - right mouse click on a sheet tab,
choose view code, choose insert / module and then on the right hand side
paste the code, close the code window using the top X (top RHS), and hide
the personal macro workbook by choosing window / hide)
then choose tools / macro / macros, click on PERSONAL.XLS!InsertTimeWithSecs
(if you can't see it change the Macros In drop down to all open workbooks)
and then click on the Option button - you can assign a short cut key here.

as to your second question about changing a formula (=NOW()) into a value,
click on cell with formula & copy it, now choose edit / paste special -
click the value radio button & then Ok.

Hope this helps
Cheers
JulieD
 
Top