Convert time data from excel format to milliseconds.

HI

I have a problem where i am trying to import time data from the excel format (0.914513888888889), into the Hour, Minute, Second< Millisecond format ('21:56:54:000').

The only problem is that the data i have from excel does not actually hold the information about the milliseconds. I have tried multiple ways of trying to perform a calculation on the data to extract some kind of millisecond data but can not figure it out.

I would also prefer if the time data was not stored as a string.

I will wattch my code. some parts are commented out as these parts i was attempting to use to get the millisecond data.

Any help would be hugley appreciated.

clc;
clear;
close all;
All_Data = {};  % set up cell array for all workbook data
for i = 1:11  % set up loop to run for 11 iterations (no. of workbooks)
    filename = sprintf('%03d.xlsx', i); % set up each filename 001-011 to be read on each iteration
    opt = detectImportOptions(filename); % import data from filename
    sheets = sheetnames(filename);  % retrieve names of sheets
    for j = 1:numel(sheets)  % iterate over each sheet within a workbook 
        tBC = readtable(filename, opt, 'Sheet', sheets{j});  % extract sheets from workbooks
        Time_Data = tBC{:, 'Var4'};  % extract time data from column 4
        ECG_Data = tBC{:, 'Var6'};  % extract ECG data from column 6
        PPG_Data = tBC{:, 'Var8'};  % extract PPG data from column 8
        SYS_Data = tBC{:, 'Var10'};  % extract reference systolic pressure data from column 10
        DIA_Data = tBC{:, 'Var12'};  % extract reference diastolic pressure data from column 12
        
        % Convert time data to the desired format
        Time_Formatted = datestr(Time_Data, 'HH:MM:SS:fff'); % Format time data with milliseconds
        % Time_Formatted = cellstr(Time_Formatted); 
        % Time_Formatted = split(Time_Formatted,':');
        % Time_Formatted = str2cell(Time_Formatted);
        
        
        %MS = ((((Time_Formatted/60)/60)/1000));
        % Convert numerical data to cell arrays
        Time_Data_Cell = num2cell(Time_Data);
        ECG_Data_Cell = num2cell(ECG_Data);
        PPG_Data_Cell = num2cell(PPG_Data);
        SYS_Data_Cell = num2cell(SYS_Data);
        DIA_Data_Cell = num2cell(DIA_Data);
        
        % Place the formatted time data and numerical data in the first row
        All_Data{i, j} = [cellstr(Time_Formatted), ECG_Data_Cell, PPG_Data_Cell, SYS_Data_Cell, DIA_Data_Cell];
        % All_Data{i, j} = [Time_Data_Cell, ECG_Data_Cell, PPG_Data_Cell, SYS_Data_Cell, DIA_Data_Cell];
    end
end

[Stephen23]

n = 0.914513888888889;
d = days(n);
d.Format = 'hh:mm:ss.SSS'

d = duration
   21:56:54.000

"The only problem is that the data i have from excel does not actually hold the information about the milliseconds."

So in lieu of actual data you just want to invent some millisecond values?

[NeedHelp55]

Thanks for getting back to me.

Not invent but i thought if i could divide each second by the number of readings i have for it, i could obtain some kind of usable data.

That is what im curently reading out at the minute. I have to plot the second two columns against the time. That is why i am looking for a way of creating the milliseconds.

[Stephen23]

You can use COLON and LINSPACE with both DURATION and DATETIME values:

linspace(seconds(0),seconds(1),7)

ans = 1×7 duration array
         0 sec   0.16667 sec   0.33333 sec       0.5 sec   0.66667 sec   0.83333 sec         1 sec

linspace(datetime,datetime+minutes(1),4)

ans = 1×4 datetime array
   01-Mar-2024 10:33:36   01-Mar-2024 10:33:56   01-Mar-2024 10:34:16   01-Mar-2024 10:34:36

Do you know the start&end times (and presumably how many times) or e.g. the start and step size?

[NeedHelp55]

there is not an exact fixed amount of readings per second but they all seem to be in the 57 - 63 range

[Stephen23]

You could count them using e.g. HISTCOUNTS or UNIQUE or something of that kind.

Or you might be able to get RETIME or similar to do this.

Without uploaded data I cannot help you much more.

[NeedHelp55]

I have attatched one of the workbooks containing the time data if that is the kind of data you require.

[Stephen23]

Were the data originally sampled at a regular interval?

Answer 1

[Mathieu NOE]

hello

so when we look at the raw time data , it's like a rounded signal after quantization

why not simply do a linear fit so we recover the lost resolution ?

from there you get back your milliseconds

Time_Formatted = 1592×12 char array

'21:56:54:350'

'21:56:54:367'

'21:56:54:384'

'21:56:54:401'

I just wonder if the initial time stamp is correct - you may have to tune that if it's important to you

clc;
clear;
close all;
All_Data = {};  % set up cell array for all workbook data
for i = 1:11  % set up loop to run for 11 iterations (no. of workbooks)
    filename = sprintf('%03d.xlsx', i); % set up each filename 001-011 to be read on each iteration
    opt = detectImportOptions(filename); % import data from filename
    sheets = sheetnames(filename);  % retrieve names of sheets
    for j = 1:numel(sheets)  % iterate over each sheet within a workbook 
        tBC = readtable(filename, opt, 'Sheet', sheets{j});  % extract sheets from workbooks
        Time_Data = tBC{:, 'Var4'};  % extract time data from column 4
        % Fit a polynomial p of 1st degree
        p = polyfit((1:numel(Time_Data)),Time_Data,1);
        Time_Data_new = polyval(p,(1:numel(Time_Data)));
        
        ECG_Data = tBC{:, 'Var6'};  % extract ECG data from column 6
        PPG_Data = tBC{:, 'Var8'};  % extract PPG data from column 8
        SYS_Data = tBC{:, 'Var10'};  % extract reference systolic pressure data from column 10
        DIA_Data = tBC{:, 'Var12'};  % extract reference diastolic pressure data from column 12
        
        % Convert time data to the desired format
%         Time_Formatted = datestr(Time_Data, 'HH:MM:SS:fff'); % Format time data with milliseconds
        Time_Formatted = datestr(Time_Data_new, 'HH:MM:SS:fff'); % Format time data with milliseconds
%         Time_Formatted = cellstr(Time_Formatted); 
%         Time_Formatted = split(Time_Formatted,':');
%         Time_Formatted = str2cell(Time_Formatted);
        
        
        %MS = ((((Time_Formatted/60)/60)/1000));
        % Convert numerical data to cell arrays
        Time_Data_Cell = num2cell(Time_Data);
        ECG_Data_Cell = num2cell(ECG_Data);
        PPG_Data_Cell = num2cell(PPG_Data);
        SYS_Data_Cell = num2cell(SYS_Data);
        DIA_Data_Cell = num2cell(DIA_Data);
        
        % Place the formatted time data and numerical data in the first row
       All_Data{i, j} = [cellstr(Time_Formatted), ECG_Data_Cell, PPG_Data_Cell, SYS_Data_Cell, DIA_Data_Cell];
%          All_Data{i, j} = [Time_Data_Cell, ECG_Data_Cell, PPG_Data_Cell, SYS_Data_Cell, DIA_Data_Cell];
    end
end

[NeedHelp55]

Hi I tried that code and i am getting the error :

Error using dateformverify

DATESTR failed converting date number to date vector. Date number out of range.

Error in datestr (line 204)

S = dateformverify(dtnumber, dateformstr, islocal);

Error in Test (line 23)

Time_Formatted = datestr(Time_Data_new, 'HH:MM:SS:fff'); % Format time data with milliseconds

Is this what you meant when you said "I just wonder if the initial time stamp is correct - you may have to tune that if it's important to you" as i am not sure how i would go about fixing this.

[Mathieu NOE]

hello again

no this is a different topic. I was simply wondering if in your coarse time signal the "valid" values are only the firts value of each stair case. This would require a small y shift of the interpolated curve (not very complicated).

Your error has nothing to do with that

can you share the excel file that generated that error ? there might be a cell that is not filled correctly so the code may not work in that case . We may have to robustify it.

[Mathieu NOE]

FYI

this is my suggestion, according to my comment above

I think the interpolated time line should be like this :

code

All_Data = {};  % set up cell array for all workbook data
for i = 1:11  % set up loop to run for 11 iterations (no. of workbooks)
    filename = sprintf('%03d.xlsx', i); % set up each filename 001-011 to be read on each iteration
    opt = detectImportOptions(filename); % import data from filename
    sheets = sheetnames(filename);  % retrieve names of sheets
    for j = 1:numel(sheets)  % iterate over each sheet within a workbook 
        tBC = readtable(filename, opt, 'Sheet', sheets{j});  % extract sheets from workbooks
        Time_Data = tBC{:, 'Var4'};  % extract time data from column 4
        % Fit a polynomial p of 1st degree to the first points of each staircase (top curve)
        xx = (1:numel(Time_Data))';
        dTime_Data = diff(Time_Data);
        ind = 1+find(dTime_Data>max(dTime_Data)/2);
        p = polyfit(xx(ind),Time_Data(ind),1);
        % Evaluate the fitted polynomial p and plot:
        Time_Data_new = polyval(p,xx);
        
        ECG_Data = tBC{:, 'Var6'};  % extract ECG data from column 6
        PPG_Data = tBC{:, 'Var8'};  % extract PPG data from column 8
        SYS_Data = tBC{:, 'Var10'};  % extract reference systolic pressure data from column 10
        DIA_Data = tBC{:, 'Var12'};  % extract reference diastolic pressure data from column 12
        
        % Convert time data to the desired format
%         Time_Formatted = datestr(Time_Data, 'HH:MM:SS:fff'); % Format time data with milliseconds
        Time_Formatted = datestr(Time_Data_new, 'HH:MM:SS:fff'); % Format time data with milliseconds
%         Time_Formatted = cellstr(Time_Formatted); 
%         Time_Formatted = split(Time_Formatted,':');
%         Time_Formatted = str2cell(Time_Formatted);
        
        
        %MS = ((((Time_Formatted/60)/60)/1000));
        % Convert numerical data to cell arrays
        Time_Data_Cell = num2cell(Time_Data);
        ECG_Data_Cell = num2cell(ECG_Data);
        PPG_Data_Cell = num2cell(PPG_Data);
        SYS_Data_Cell = num2cell(SYS_Data);
        DIA_Data_Cell = num2cell(DIA_Data);
        
        % Place the formatted time data and numerical data in the first row
       All_Data{i, j} = [cellstr(Time_Formatted), ECG_Data_Cell, PPG_Data_Cell, SYS_Data_Cell, DIA_Data_Cell];
%          All_Data{i, j} = [Time_Data_Cell, ECG_Data_Cell, PPG_Data_Cell, SYS_Data_Cell, DIA_Data_Cell];
    end
end

[NeedHelp55]

Yes no problem and thanks for the reply. This is the sheet I tried it on where I got the error.

[Mathieu NOE]

I see

I was a bit too fast and tested the code only on the first sheet of your excel file

I see now some sheets have extra lines after the valid data section

this is sheet #5 :

those trailing lines must be removed from your tBC table before moving on

tBC before correction :

so my first version of the code with the correction in place is :

clc;
clear;
close all;
All_Data = {};  % set up cell array for all workbook data
for i = 1:11  % set up loop to run for 11 iterations (no. of workbooks)
    filename = sprintf('%03d.xlsx', i); % set up each filename 001-011 to be read on each iteration
    opt = detectImportOptions(filename); % import data from filename
    sheets = sheetnames(filename);  % retrieve names of sheets
    for j = 1:numel(sheets)  % iterate over each sheet within a workbook 
        tBC = readtable(filename, opt, 'Sheet', sheets{j});  % extract sheets from workbooks
        Time_Data = tBC{:, 'Var4'};  % extract time data from column 4
        
        % Remove invalid rows from table (corresponding to NaN's in Time_Data
        rows2delete = isnan(Time_Data);
        tBC(rows2delete,:) = [];
        
        Time_Data = tBC{:, 'Var4'};  % extract time data from column 4
        % Fit a polynomial p of 1st degree to the first points of each staircase (top curve)
        xx = (1:numel(Time_Data))';
        p = polyfit(xx,Time_Data,1);
        % Evaluate the fitted polynomial p and plot:
        Time_Data_new = polyval(p,xx);
        
%         % debug plots (to double check)
%         figure(j)
%         plot(xx,Time_Data,xx,Time_Data_new); 
        
        ECG_Data = tBC{:, 'Var6'};  % extract ECG data from column 6
        PPG_Data = tBC{:, 'Var8'};  % extract PPG data from column 8
        SYS_Data = tBC{:, 'Var10'};  % extract reference systolic pressure data from column 10
        DIA_Data = tBC{:, 'Var12'};  % extract reference diastolic pressure data from column 12
        
        % Convert time data to the desired format
%         Time_Formatted = datestr(Time_Data, 'HH:MM:SS:fff'); % Format time data with milliseconds
        Time_Formatted = datestr(Time_Data_new, 'HH:MM:SS:fff'); % Format time data with milliseconds
%         Time_Formatted = cellstr(Time_Formatted); 
%         Time_Formatted = split(Time_Formatted,':');
%         Time_Formatted = str2cell(Time_Formatted);
        
        
        %MS = ((((Time_Formatted/60)/60)/1000));
        % Convert numerical data to cell arrays
        Time_Data_Cell = num2cell(Time_Data);
        ECG_Data_Cell = num2cell(ECG_Data);
        PPG_Data_Cell = num2cell(PPG_Data);
        SYS_Data_Cell = num2cell(SYS_Data);
        DIA_Data_Cell = num2cell(DIA_Data);
        
        % Place the formatted time data and numerical data in the first row
       All_Data{i, j} = [cellstr(Time_Formatted), ECG_Data_Cell, PPG_Data_Cell, SYS_Data_Cell, DIA_Data_Cell];
%          All_Data{i, j} = [Time_Data_Cell, ECG_Data_Cell, PPG_Data_Cell, SYS_Data_Cell, DIA_Data_Cell];
    end
end

[Mathieu NOE]

and this is the second version of the code (with Time_Data_new "on top" of the staircase signal Time_Data)

here this is how it looks like for sheet #5

clc;
clear;
close all;
All_Data = {};  % set up cell array for all workbook data
for i = 1:11  % set up loop to run for 11 iterations (no. of workbooks)
    filename = sprintf('%03d.xlsx', i); % set up each filename 001-011 to be read on each iteration
    opt = detectImportOptions(filename); % import data from filename
    sheets = sheetnames(filename);  % retrieve names of sheets
    for j = 1:numel(sheets)  % iterate over each sheet within a workbook 
        tBC = readtable(filename, opt, 'Sheet', sheets{j});  % extract sheets from workbooks
        Time_Data = tBC{:, 'Var4'};  % extract time data from column 4
        
        % Remove invalid rows from table (corresponding to NaN's in Time_Data
        rows2delete = isnan(Time_Data);
        tBC(rows2delete,:) = [];
        
        Time_Data = tBC{:, 'Var4'};  % extract time data from column 4
        % Fit a polynomial p of 1st degree to the first points of each staircase (top curve)
        xx = (1:numel(Time_Data))';
        dTime_Data = diff(Time_Data);
        ind = 1+find(dTime_Data>max(dTime_Data)/100);
        ind = ind(2:end); % in some cases the first point is too much off trend and we prefer not to use it.
        p = polyfit(xx(ind),Time_Data(ind),1);
        
        % Evaluate the fitted polynomial p and plot:
        Time_Data_new = polyval(p,xx);
        
%         % debug plots (to double check)
%         figure(j)
%         plot(xx,Time_Data,xx,Time_Data_new); 
        
        ECG_Data = tBC{:, 'Var6'};  % extract ECG data from column 6
        PPG_Data = tBC{:, 'Var8'};  % extract PPG data from column 8
        SYS_Data = tBC{:, 'Var10'};  % extract reference systolic pressure data from column 10
        DIA_Data = tBC{:, 'Var12'};  % extract reference diastolic pressure data from column 12
        
        % Convert time data to the desired format
%         Time_Formatted = datestr(Time_Data, 'HH:MM:SS:fff'); % Format time data with milliseconds
        Time_Formatted = datestr(Time_Data_new, 'HH:MM:SS:fff'); % Format time data with milliseconds
%         Time_Formatted = cellstr(Time_Formatted); 
%         Time_Formatted = split(Time_Formatted,':');
%         Time_Formatted = str2cell(Time_Formatted);
        
        
        %MS = ((((Time_Formatted/60)/60)/1000));
        % Convert numerical data to cell arrays
        Time_Data_Cell = num2cell(Time_Data);
        ECG_Data_Cell = num2cell(ECG_Data);
        PPG_Data_Cell = num2cell(PPG_Data);
        SYS_Data_Cell = num2cell(SYS_Data);
        DIA_Data_Cell = num2cell(DIA_Data);
        
        % Place the formatted time data and numerical data in the first row
       All_Data{i, j} = [cellstr(Time_Formatted), ECG_Data_Cell, PPG_Data_Cell, SYS_Data_Cell, DIA_Data_Cell];
%          All_Data{i, j} = [Time_Data_Cell, ECG_Data_Cell, PPG_Data_Cell, SYS_Data_Cell, DIA_Data_Cell];
    end
end

[NeedHelp55]

Thank you so much that has worked exactly like i wanted it to.

[Mathieu NOE]

Glad I could help !

have a good day

[NeedHelp55]

Hi i have been trying to do something with this code and just wondered if you could help me again sorry! Do you know how i would turn the variable "Time_Formatted" from char to a double or a num? At the minute is is a ( however many rows x 12 char ). This is only a problem because MATLAB will not let me plot it against "PPG_Data_Cell" or "ECG_Data_Cell".

[Stephen23]

Do not use CHAR or DOUBLE to store or plot the time, use DATETIME:

https://www.mathworks.com/help/matlab/ref/datetime.html

DATETIME will in most cases plot with nicely selected date/time ticke marks (whereas your suggested data types won't). Avoid deprecated DATESTR and DATENUM.

[NeedHelp55]

Thank you for getting back to me.

I have tried this but am being told i am using the wrong number of arguments.

I have updated the line to

"Time_Formatted = datetime(Time_Data_new,'ConvertFrom','excel')"

I cannot find a correct dateType funtion to store the data i want it. It does not have the milliseconds.

When is use using "datestr" the data was how i wanted it just i could not plot it.

[Steven Lord]

Does it not store the milliseconds or does it just not display the milliseconds by default?

dt = datetime('now')

dt = datetime
   05-Mar-2024 17:11:12

What Format is used to display "right now" by default?

fmt = dt.Format

fmt = 'dd-MMM-uuuu HH:mm:ss'

Let's add in the appropriate characters to display milliseconds. I'll use three capital S characters, you could use anywhere between 1 and 9 to display fractional seconds.

dt.Format = fmt + ".SSS"

dt = datetime
   05-Mar-2024 17:11:12.355

If you want you can set the Format property as you construct the datetime array.

dt2 = datetime('now', 'Format', 'dd-MMM-uuuu HH:mm:ss.SSS')

dt2 = datetime
   05-Mar-2024 17:11:12.631

[Mathieu NOE]

@NeedHelp55

you can convert your date (string) to datetime values like this :

t = datetime(Time_Formatted,'InputFormat','HH:mm:ss:SSS');
t.Format = 'HH:mm:ss.SSS';

[Stephen23]

"It does not have the milliseconds."

Did you change the format to display the milliseconds?

"I have tried this but am being told i am using the wrong number of arguments."

It works here on this forum:

p = [3 2 1];
x = [5 7 9];
y = polyval(p,x)

y = 1×3

86 162 262

d = datetime(y,'ConvertFrom','excel')

d = 1×3 datetime array
   26-Mar-1900   10-Jun-1900   18-Sep-1900

But because you did not show us the complete error message you have made it very difficult for us to help you debug what you did wrong. Please show us all of the red text.

Avoid deprecated DATESTR and DATENUM.