How to get current date and time with milliseconds in Excel?
As you may learned in other tutorials, you might know that you can press Ctrl + ; (semicolon) to get the current date, and press Ctrl + Shift + ; (semicolon) to get the current time in Excel. However, the date and time have to be in two cells, and the time you get with the method only contains hours and minutes, which means that the seconds will always be zero.
In this tutorial, we will talk about how to have the date and time in one cell, and display milliseconds in the date and time as well. Please read on to find out how.
Get current date and time with milliseconds with NOW function
To insert the current date and time with milliseconds, you can use NOW function as shown below:
1. Select the cells in which you will enter the current time with milliseconds, and then press Ctrl + 1 to open the Format Cells dialog.
2. In the Format Cells dialog, on the Number tab, click on Custom, enter m/d/yyyy h:mm:ss.000 in the Type box, and click OK to close the dialog.
3. Enter the formula below in a cell, then double click on the result cell, and press F9 to insert the current time.
=NOW()
Get current date and time with milliseconds by clicking on a button
If you need to get the date and time at a particular moment when a specific action is made, say, clicking on a button. The following macro will do the trick nicely:
1. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module. Then copy and paste the following VBA code to the Module window.
VBA code: Quickly insert date and timestamp in Excel
Sub TimeStampEO()
'Update by ExtendOffice
Selection.NumberFormatLocal = "m/d/yyyy h:mm:ss.000"
ActiveCell.Value = Format(Now, "m/d/yyyy h:mm:ss") & Right(Format(Timer, "0.000"), 4)
End Sub
3. Go back to your worksheet. Click on Insert > Shapes, and then insert a shape you like in the worksheet.
4. Double-click on the shape you just inserted to add text, say, Date and timestamp, to the shape. If you want, you can change the shape style or text style.
5. Right click on the shape, and select Assign Macro on the drop-down menu.
6. In the pop-up Assign Macro dialog box, select TimeStampEO in the Macro name list, and select This Workbook in the Macros in drop-down list, and then click on OK.
7. Now, you just need to select a cell in which you will insert the current date and time with milliseconds, and then click on the shape. The date and time will be inserted at once.
- If you already have times in a worksheet, and want to convert them to the times with milliseconds, you just need to press Ctrl + 1 to open the Format Cells dialog and do as the first method described.
- Excel does not support formatting times with nanoseconds or microseconds, if you need to have time display nanoseconds or microseconds, you should format the time to text and then enter the time with nanoseconds or microseconds.
Get current date and time with milliseconds by clicking on a button
Related articles
How To Convert Dd Hh Mm Time Format To Hours Or Minutes In Excel?
Supposing you have a list of date format as DD:HH:MM which means days, hours, minutes, but now, you need to convert this to hours or minutes. This article will talk about how to convert the DD:HH:MM time format to hours or minutes in Excel.
How To Convert Decimal Hours/Minutes To Time Format In Excel?
In some cases, you may record time as decimal hours or minutes such as this 31.23, how can you convert this decimal hours or decimal minutes to standard time format hh:mm:ss as below screenshot shown. In this article, I introduce some tricks for you to quickly finish this conversion in Excel.
How To Convert Time Format From 12 Hour To 24 Hour And Vice Versa In Excel?
When you work on Excel in your daily work, you may use different time format under different conditions, such as 12 hour format and 24 hour format as following screenshot shown. But, how could you convert time format from 12 hour to 24 hour and vice versa in Excel?
How To Display Time In Minutes Format Only In Excel?
Supposing you have a list of HH:MM:SS time format cells, and now you want to display this time cells in minutes format only. What can you do? In this tutorial, we will show you two methods of displaying time in minutes format only in Excel.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
