Excel automatically changing the format of timestamps for a movie subtitles

Copper Contributor

I can't find a way to work around it. Tried changing settings in the Autocorrect, didn't work.

 

Zrzut ekranu 2024-01-12 175554.png

 

As can be seen in the middle of the screenshot, I tried typing in the format that was used throughout these subtitles, but it doesn't work. Excel decides to change it to a different format, which is infuriating, because I need the microseconds, too.

 

Sorry if this is a silly problem, I tried looking it up and didn't find any solution for it, so I decided to post it here. Thank you for your understanding.

2 Replies
Use hh:mm:ss,000 as a custom number format.

@JanBC  wrote: ``Excel decides to change it to a different format, which is infuriating, because I need the microseconds, too``

 

You keep saying that Excel changes the format, but it is unclear exactly what you typed and how you want the result to be displayed or what is wrong with the displayed format.

 

Please clarify.

 

If you simply do not want zero hours to be displayed, change the format to Custom [mm]:ss,000 .

 

The square-bracketed [mm] will display minutes greater than 59, on purpose or by accident.

 

-----

Also, you say that you want "microseconds, too".  Do you really mean "milliseconds"?

 

In the time displayed as 00:44:03,171 , that is 171 milliseconds, not microseconds.

 

If you mean that time is entered and should be displayed with greater precision (e.g. 44:03,171567), note that Excel will not format the microseconds (567).

 

(But a calculated time value can be (the binary approximation of) 44:03,171567 . )

 

In fact, if we enter literally 44:03,171567 , Excel rounds(!) to the millisecond.  Thus, the time value is actually (the binary approximation of) 44:03,172.