Convert time data from excel format to milliseconds.

30 views (last 30 days)
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
  7 Comments
NeedHelp55
NeedHelp55 on 1 Mar 2024
I have attatched one of the workbooks containing the time data if that is the kind of data you require.
Stephen23
Stephen23 on 1 Mar 2024
Were the data originally sampled at a regular interval?

Sign in to comment.

Accepted Answer

Mathieu NOE
Mathieu NOE on 1 Mar 2024
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
  14 Comments
Mathieu NOE
Mathieu NOE on 5 Mar 2024
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
Stephen23 on 5 Mar 2024
Edited: Stephen23 on 5 Mar 2024
"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.

Sign in to comment.

More Answers (0)

Products


Release

R2022b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!