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

Question

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()

Answer 1

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.

Answer 2

That "circular references" error is more related to a mistake in the formula than the cell formatting. This formula could answer your question:

=MOD(MOD(A1;1)*24*60*60*1000;1000)

This assumes that your date is in cell A1.

Remember that dates in Excel are numbers: The integer part represents the date and the decimal part represents intraday measures (hours, minutes, seconds and milliseconds)