How to change HH:mm:ss:SSS to HH:mm:ss.SSS for a column?

Hi, I'm collecting a data .txt file with multiple columns. One column is time but the issue is that the software I use exports the time data as HH:mm:ss:SSS (Note the last semicolon should be a period) and theres around 70,000 data points. I'm using readtable to create graphs. My question is: How to convert each data point of time in the column from HH:mm:ss:SSS to HH:mm:ss.SSS? Thank you for your help and insight.

[Walter Roberson]

Could you confirm that the data is currently read in as cell array of character vectors stored in the table?

[Lucas]

The data is read using the readtable function.

Answer 1

[Walter Roberson]

time = {'01:18:34:754'; '13:04:19:999'}

time = 2×1 cell array
    {'01:18:34:754'}
    {'13:04:19:999'}

converted = duration(string(datetime(time, 'InputFormat', 'HH:mm:ss:SSS', 'Format', 'HH:mm:ss.SSS')), 'format', 'hh:mm:ss.SSS')

converted = 2×1 duration array
   01:18:34.754
   13:04:19.999

[Walter Roberson]

Unfortunately, duration() will only accept a small number of InputFormat, so the conversion cannot be direct.

[Walter Roberson]

time = {'01:18:34:754'; '13:04:19:999'}

time = 2×1 cell array
    {'01:18:34:754'}
    {'13:04:19:999'}

temp = char(time);
temp(:,end-3) = '.';

converted = duration(cellstr(temp), 'Format', 'hh:mm:ss.SSS')

converted = 2×1 duration array
   01:18:34.754
   13:04:19.999

Answer 2

[chicken vector]

if the time format is imported from the .txt as a string, you can do the following using a for loop for every element:

time = '01:18:34:754';
semicolons = strfind(time, ':');
time(semicolons(end)) = '.'

time =

'01:18:34.754'

To speed things up, since the format is standardsed, you can also do directly:

time(9) = '.';

Otherwise you need to specify what format are you working with.

Also I suggest you to have a look at duration which you may find helpful.

[Walter Roberson]

This needs a bit of modification for entries coming from a table, as those would either be a cell array of character vectors (more likely) or a string array (only if specifically configured for.)

[chicken vector]

You are right, I was trying not to speculate too much given the little information provided, but assuming a cell array is definetly the right choice.

Answer 3

[Stephen23]

time = {'01:18:34:754'; '13:04:19:999'};
data = rand(2,1);
T = table(time,data)

T = 2×2 table
          time           data  
    ________________    _______

    {'01:18:34:754'}    0.73654
    {'13:04:19:999'}    0.68067

tmp = regexprep(T.time,':(?=\d{3})','.');
T.time = duration(tmp, 'Format','hh:mm:ss.SSS')

T = 2×2 table
        time         data  
    ____________    _______

    01:18:34.754    0.73654
    13:04:19.999    0.68067