Search code examples
excelpandasdatetimemilliseconds

Getting just milliseconds from date formatted MM/DD/YYYY HH:MM:SS.000 in excel

Trying to obtain just the milliseconds from a cell that has the format MM/DD/YYYY HH:MM:SS.000 PM. Most equations give an error that says:

"There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly."

Image of an example formatted cell

UPDATE:

Solved the problem by writing a Python script using the pandas and time libraries. Assuming that the time data is in the A column, this code should work by creating a new excel file named output.xlsx:

import pandas as pd 
import time
df = pd.read_excel('firstFile.xlsx', usecols = "A")
writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter')
df.Time = pd.to_datetime(df.Time)
df.Time = df.Time.dt.microsecond
df.Time = df.Time - df.Time[0]
df.to_excel(writer, sheet_name = 'Sheet1')
writer.save()
Solution

  • The following formula seems to work:

    =RIGHT(TEXT(A1, "hh:mm:ss.000"),3)/1000

    This assumes that your date is in cell A1, and it works by converting to text and then extracting the millisecond component at the end.