Friday, May 20, 2011

Tips: How to enter the time in MS Excel as HH:MM:SS

As everyone knows, we can get the time in Excel by using the Ctrl + Shift + ; keys. However, if we use these shortcut keys, we can get the time in HH:MM format. Therefore, if you wish to get the time format as HH:MM:SS (00:00:00), you can follow the following steps. While performing these steps, the main thing you’ve to note is that there are no short cut keys available to get the time format HH:MM:SS (00:00:00). However, we can create a macro and assign it to your shortcut key. You can also use the same shortcut keys that you use for getting the time.
Now, let’s check how to create a macro for setting the time.

•    First, click on Tools on the top menu to select Macro.
•    Now, select Visual Basic Editor.
•    After that, it is required to select Module from the Insert on the top menu.
•    Once you’ve performed the above steps, it is required to copy and paste the following code.

Sub TimeWithSeconds()
ActiveCell.Value = Time
ActiveCell.NumberFormat = "h:mm:ss"
End Sub

•    Now, click on File on the top menu and then save the file.
•    Then, you can close the Visual Basic Editor.

If you wish to add this Macro to a shortcut key, you can follow the following steps.

•    As usual, click on Tools from the menu bar.
•    Now, select Macro and then select Macros.
•    Then, select the TimeWithSeconds Macro.
•    After that, it is required to click on Options.
•    Then, enter the shortcut key you would like to use.
•    Now, click on OK.

That’s it. All it is done. Now, you should be able to use the shortcut key to enter the time with the current seconds.