Time in Excel '97

  • Thread starter Thread starter Matt J.
  • Start date Start date
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.
 
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
(e-mail address removed)
 
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
 
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
 
Why not just use Alt+F11 to go to the VBE and skip all the unhiding and
hiding?

Select Personal.xls in the project explorer to make it the active project,
then do Insert=>Module
 
Back
Top