You may have time values in Excel displayed in hours, minutes, and seconds, and you may want to add milliseconds to the display. Milliseconds are put in a format that is recognized in Excel. A custom format should also be assigned to cells so that you can read the values easily.
Let us look at time formats with milliseconds that Excel recognizes:
Excel usually stores time values as fractions of the day by default. For example, 12h is represented as 12/24 or ½. 1millisecond will be displayed as a tiny value, that is, 1/86400000. Though this way of displaying time in Excel is easy, it is not user-friendly.
In this regard, Excel also uses the following format to show time; hh: mm: ss. 000. The numbers read in this format can also be converted into the fraction of the day. You will, however, be required to assign them a corresponding custom format so that they are displayed correctly.
Using Custom Cell format with milliseconds
Use the following method;
1. Open the CSV file in Excel
2. Select the column with the time
3. Right-click and select Format Cells.
You can also show time with milliseconds by the Format Cells Feature. The following easy steps will help you;
Showing time with Milliseconds by the Format Cells Feature
1. Select the cells that contain time.
2. Right- Click anywhere in your worksheet
3. Click on Format Cells
4. You can also use the keyboard shortcut CTRL+ SHIFT+ F
5. In the Format Cells window, choose the Number tab
6. Click on Custom from Category
7. Enter h:mm:ss:000 in the typing box that appears
8. Click OK
It displays all the time values, including the milliseconds, as decimals. It is also possible to add whole hours or decimals to be displayed in Excel. The Start_Time+ TIME and Start_Time+hours/ s2functions can be used.
Showing Milliseconds only
You can use the formula: =RIGHT (TEXT(D2, "hh: mm:ss.000"), 3)*1. The three rightmost characters are taken from the D2 cell and multiplied by 1. It ensures the result is taken as a number. To extract the milliseconds, format the cells in the result column as a number without any decimal places.
Adding whole hours
The following formula can be used; =B2+ TIME (C2, 0, 0) or =B2+ TIME (C2, 0, 0)
Start_time: This is the time that you want to add hours to
Hours: these are the hours that you want to add
To subtract the hours you have from a certain period, you will use the following formula; =MOD (Start_time_TIME (hours, 0, 0), 1)
Adding decimal hours to time
The general formula that can be used in this case is as follows; +Start_time+ hours/24
The Start_time is the time that you want to add hours to
The Hours are the hours you will add to the start_time.
The value that you get should be transformed back to time format. To see the results, press Enter.