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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

ProfessorJimJamMicrosoft Excel ExpertCommented:
put =NOW()  and then format as  h:mm:ss AM/PM
ProfessorJimJamMicrosoft Excel ExpertCommented:
to change the format, right click on cell and > Format Cells then select Custom and put h:mm:ss AM/PM
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...
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

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
Saqib Husain, SyedEngineerCommented:
After you do the Shift-ctr-; in the same cell type =now()[F9] then press enter
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?
Saurabh Singh TeotiaCommented:
On the worksheet where you get this error is that worksheet password protected?
VitalyNagyAuthor Commented:
Yes, and it must be this way.
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...
VitalyNagyAuthor Commented:
How to hide the module with the open password in such case?
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...

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.