Convert time data from excel format to milliseconds.
23 次查看(过去 30 天)
显示 更早的评论
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 个评论
采纳的回答
Mathieu NOE
2024-3-1
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 ?
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/1632401/image.png)
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 个评论
Mathieu NOE
2024-3-5
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
2024-3-5
编辑:Stephen23
2024-3-5
"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)
d = datetime(y,'ConvertFrom','excel')
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.
更多回答(0 个)
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Standard File Formats 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!