Note: The other languages of the website are Google-translated. Back to English

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.

Note: If you don’t need to show milliseconds, you should use this format code: m/d/yyyy h:mm:ss.

3. Enter the formula below in a cell, then double click on the result cell, and press F9 to insert the current time.

=NOW()

Note: Once you have entered the formula above, the date and time when you entered the formula will be inserted in the cell. However, since the result of NOW function will update each time you make changes to the worksheet, you will have to double click on the result cell and press F9 to insert the current date and time again and keep the value from updating.


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.

Note:
  1. 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.
  2. 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.


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations