Keyboard input of exact time in Excel

The standard keyboard shortcut to put time into a cell (Shift+Ctrl+;) inserts time accurate to a minute (with seconds always :00). Is there a way to automatically put in time accurate to a second?
VitalyNagyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamCommented:
put =NOW()  and then format as  h:mm:ss AM/PM
0
ProfessorJimJamCommented:
to change the format, right click on cell and > Format Cells then select Custom and put h:mm:ss AM/PM
0
Saurabh Singh TeotiaCommented:
The shortcut you have will only give you time without seconds...

However their are two ways that you can do this...

first..enter the formula as =now() in excel and format it as hh:mm:ss that will do what you are looking for...

Alternatively you can enter this code in your module and use ctrl+y as the shortcut for the same...

Sub Inserttime()
'
' 
'
' Keyboard Shortcut: Ctrl+y
'
    Selection.Value = Time
    
    Selection.NumberFormat = "h:mm:ss"
End Sub

Open in new window


Saurabh...
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Rob HensonFinance AnalystCommented:
Are you open to formula or VBA options instead?

Formula driven as suggested by PJJ will be dynamic, ie will change when recalculated.

Also, =NOW() will also put date portion, using the following will just put time:

=MOD(NOW(),1)

You can then copy and paste values to make static.

If its a specific cell that will have the value, you could set a worksheet change event to overwrite with value.

Thanks
Rob H
0
Saqib Husain, SyedEngineerCommented:
After you do the Shift-ctr-; in the same cell type =now()[F9] then press enter
0
VitalyNagyAuthor Commented:
Thank you, Saurabh,

Your method generally works, but I also get an error message in the window "Microsoft Visual Basic": Runtime Error '1004': Unable to set the NumberFormatproperty of the Range class.

How can I get rid of it?
0
Saurabh Singh TeotiaCommented:
On the worksheet where you get this error is that worksheet password protected?
0
VitalyNagyAuthor Commented:
Yes, and it must be this way.
0
Saurabh Singh TeotiaCommented:
Then in that case..you can add a line in the code... to unprotect it first then change the format and protect it again... for instance like this...

Sub Inserttime()
'
'
'
' Keyboard Shortcut: Ctrl+y
'
ActiveSheet.Unprotect Password:="abc"

    Selection.Value = Time
    
    Selection.NumberFormat = "h:mm:ss"
    
    ActiveSheet.Protect Password:="abc"
End Sub

Open in new window


I assumed that your sheet password is abc...

Saurabh...
0
VitalyNagyAuthor Commented:
How to hide the module with the open password in such case?
0
Saurabh Singh TeotiaCommented:
Go to vbaproject-->right click on it-->protection-->checkbox the lock for viewing.. Give your password and hit ok...

Save the workbook and close it..Next time when you open the vba editor it will ask for password in order to view the code...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.