Hi all,
I am working on a project where I need to use an exact time stamp where milliseconds are required I am trying to subtract one cell from another where the time stamp looks like this HH:MM:SS:MSS.
I have attached my spreadsheet for review forum.ozgrid.com/index.php?attachment/33271/
Re: Help required using milliseconds
The final separator - between seconds and milliseconds - should be a dot (full stop, period), not a colon.
Re: Help required using milliseconds
Try this in Cell D5, and drag down
=TEXT(LEFT(C5,8)-LEFT(B5,8)+IF(RIGHT(C5,LEN(C5)-9)-RIGHT(B5,LEN(B5)-9)<0,-TIME(0,0,1),IF(RIGHT(C5,LEN(C5)-9)-RIGHT(B5,LEN(B5)-9)>1000,TIME(0,0,1),TIME(0,0,0))),"HH:MM:SS")&":"&TEXT(IF(RIGHT(C5,LEN(C5)-9)-RIGHT(B5,LEN(B5)-9)<0,1000+(RIGHT(C5,LEN(C5)-9)-RIGHT(B5,LEN(B5)-9)),RIGHT(C5,LEN(C5)-9)-RIGHT(B5,LEN(B5)-9)),"#00")
Re: Help required using milliseconds
That worked thanks alot. I tried putting in the period seperator as per the other suggestion but still couldnt get it to work. Is your formula just a fix for the way I displayed it?
Re: Create time stamp that includes milliseconds
Nerdbloke,
Glad to have you as part of the Ozgrid community.
However, please try to be as precise as possible when writing your thread title, keeping in mind that thread titles are critical for obtaining good search results and should be written with this thought in mind. As an example for future threads, note the change to your thread title; it is a bit more accurate relative to your stated objective. Avoid the use of non-essential words such as "help" (and many others such as "desperate", "urgent", "I need . . .", etc.)
Thanks,
Re: Create time stamp that includes milliseconds
Nerdbloke, try to quote entire posts only if absolutely necessary. Otherwise, it would result in a clutter of words. Removed the quote for you.
Yes, this is specific to the way you have displayed the time. Note that it is not a time format, but a text format.
Re: Create time stamp that includes milliseconds
see the attachment....................
Re: Create time stamp that includes milliseconds
Appreciate your help Niraj. However, the original question was for a HH:MM:SS:MilliSecond format, which doesn't work with a straight subtraction.
The original format is a text type, whereas your post has it as Time type.
Re: Create time stamp that includes milliseconds
With the custom format [hh]:mm:ss.000, Excel serial time, entered like 34:56:02.156 , can be used throughout.
If the data is stored as text, Text To Columns , with the type set to Date will convert.
Re: Create time stamp that includes milliseconds
Nice information Mike! I did not know that.
Though it still appears as 01/01/1900 10:56:02 in the formula bar which is a bit confusing and you have to be careful when changing information as Excel likes to truncate the milliseconds. Still its a very nice way to handle this.
Don’t have an account yet? Register yourself now and be a part of our community!
