How to get the current date and time with milliseconds in Excel?
When working with date and time data in Excel, it’s common to require highly detailed timestamps for tasks like tracking measurement logs, system events, or precise batch operations. Standard keyboard shortcuts, such as pressing Ctrl + ; for the current date and Ctrl + Shift + ; for the current time, can quickly input date and time values. However, these shortcuts have clear limitations: the outputs must be entered into separate cells and only return the date or the time up to the minute; seconds will always default to zero, and milliseconds are not displayed. This can be problematic in scenarios where precise time-stamping, including milliseconds, is necessary, such as scientific measurements or rapid transaction logging.
This tutorial will introduce several efficient ways to display both the current date and time—including milliseconds—in a single cell in Excel. You’ll find methods suited for ongoing worksheet tracking, instant one-click timestamping, and formula-based formatting. Continue reading for detailed steps, usage scenarios, and practical tips to help you meet your advanced time-stamping needs.
Get the current date and time with milliseconds with the NOW function
One way to insert the current date and time with milliseconds involves the use of the NOW function, combined with custom cell formatting. This solution is suitable for applications where you want to frequently update the timestamp by recalculating your worksheet. However, keep in mind that Excel stores time internally using fractions of a second, so the actual displayed millisecond value may vary depending on the system and calculation timing.
Here's how you can use this method:
1. Select the cell or range of cells where you would like to display the current date and time, including milliseconds. Then, press Ctrl + 1 to open the Format Cells dialog box.
2. In the Format Cells dialog, go to the Number tab, choose Custom from the Category list. In the Type field, enter m/d/yyyy h:mm:ss.000 then click OK. This tells Excel to show three decimal places for seconds as milliseconds.
3. Enter the formula below into your chosen cell, for example, A1. Next, if you want to lock the value instead of having it update constantly, double-click the result cell and press F9. This replaces the formula with its current value, effectively freezing the timestamp at that moment.
=NOW()
Get the current date and time with milliseconds using an Excel formula (TEXT function)
Another practical solution for displaying the current date and time with milliseconds in a user-friendly format leverages the TEXT function in Excel. This approach is ideal for quickly generating display-ready timestamps directly in a cell, and it can be particularly useful when you want to concatenate or present timestamps as part of other string outputs.
Situations where this method is valuable include logging operations, report generation, and creating time-stamped entries in forms or automated sheets. Unlike traditional date/time formatting, the TEXT function treats your timestamp as text, simplifying custom layouts or further manipulation using additional Excel functions. A key consideration: since the output is text, it cannot be used directly for time calculations, but it does provide a clean and easy-to-read result.
1. Click into the cell where you wish to show the current date and time with milliseconds, for example, B1. Type in the following formula:
=TEXT(NOW(),"m/d/yyyy h:mm:ss.000")2. Press Enter to confirm. The cell will now show the current date and time with milliseconds as a text string. You can drag the fill handle down to apply the formula to more rows if needed, or copy and paste it as required in your worksheet.
This formula displays the current date, hour, minute, and seconds, followed by three digits for milliseconds. If you want to adjust the format, you can modify the string in the formula, for example, by changing the date separator or display order according to your locale settings.
Applicable scenarios and advantages: This formula is highly recommended when you need visually consistent, easy-to-read timestamps in reports, dashboards, and logging applications without requiring built-in time arithmetic. However, since it outputs a text string, direct time calculations or sorting as numeric values may require additional steps; you may need to revert to standard date-time formats for advanced computations.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
When you want to instantly record the exact date and time, including milliseconds, at a specific moment—such as when clicking a button—Excel's VBA macro functionality provides a convenient solution. This method is particularly useful for process tracking, quick event stamping, or responsive record logging in shared sheets where timing accuracy is crucial.
To configure this macro-based approach, follow these steps:
1. Press Alt + F11 to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module. Then copy and paste the following VBA code into 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. Return to your worksheet. Click on Insert > Shapes, and add a shape (for example, a button or rectangle) to your worksheet.
4. Double-click the inserted shape to label it, such as "Date and timestamp." You may personalize the style and text to suit your sheet's design.
5. Right-click the shape, and choose Assign Macro from the dropdown menu.
6. In the Assign Macro dialog, select TimeStampEO from the Macro name list, then pick This Workbook from the drop-down. Click OK.
7. Now select the cell where you wish to insert the date and time with milliseconds, and simply click the shape. The timestamp will be entered immediately into your chosen cell.
- If you have time values already in your worksheet and wish to display their milliseconds, simply use the custom formatting described in the first method.
- Excel doesn’t support nanosecond or microsecond-level formatting in its standard time formats. If you must record finer time slices, format your cells as text and manually enter the desired time values.
For troubleshooting: If the macro button does not work, ensure macros are enabled and that you have saved your workbook as an .xlsm file. If error messages appear when running the macro, double-check that the target cell is not already in use or locked, and that you have selected the cell before clicking the assigned shape.
Summary suggestion: For accuracy in time logging, always double-check your worksheet calculation settings. If precise millisecond logging is mission-critical, consider rounding or adjusting results to suit your application's tolerance.
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 formats 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 these decimal hours or decimal minutes to standard time format hh:mm:ss, as screenshot below? 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 formats under different conditions, such as 12-hour format and 24-hour format, as following screenshot. But, how could you convert time format from12 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 these 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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in