How to convert time column from hours to decimal days to HH:MM:SS

조회 수: 1 (최근 30일)
I need to convert the first column in the excel sheet to HH:MM:SS, this is so I can plot a time series for surface water (Column B) and bottom water (Column C) temperatures.
There's two excel sheets for the years 2014 and 2015.
3 columns of data: time (days since January 1st), surface temperature, near-bed temperature
I'm not sure where to start!

채택된 답변

Star Strider
Star Strider 2020년 12월 6일
편집: Star Strider 2020년 12월 6일
Try this for the 2014 file (do essentially the same for any others), noting that with this format for ‘filename’, the code will automatically extract the years:
filename = 'ccs_temperatures2014.xlsx';
T1 = readtable(filename, 'VariableNamingRule','preserve');
fileyear = regexp(filename, '\d*', 'match');
fyear = str2double(fileyear{:});
dn = datenum([repmat([fyear 0 0], size(T1,1), 1) T1{:,1} zeros(size(T1,1),2)]);
Col1 = datetime(dn, 'ConvertFrom','datenum', 'Format','HH:mm:ss');
T2 = table(Col1);
T2 = [T2,T1(:,2:end)];
T2.Properties.VariableNames = {'Time (HH:mm:ss:)','Surface Temperature (°C)', 'Bottom Temperature (°C)'};
that with:
FirstFiveRows = T2(1:5,:)
produces:
FirstFiveRows =
5×3 table
Time (HH:mm:ss:) Surface Temperature (°C) Bottom Temperature (°C)
________________ ________________________ _______________________
13:00:00 9.5343 9.4499
13:00:12 9.5223 9.4494
13:00:24 9.5344 9.4493
13:00:37 9.5315 9.4489
13:00:50 9.5251 9.448
The dates and times were (again) the problem, however with a bit of creativity, that was relatively easily dealt with.
Here, only the 'HH:mm:ss' displays, although the full date information is retained internally.
EDIT — (6 Dec020 at 21:05)
I misread it the first time.
Change to:
dn = datenum([repmat([fyear 0], size(T1,1), 1) T1{:,1} zeros(size(T1,1),3)]);
and then:
FirstFiveRows = T2(1:5,:)
produces:
FirstFiveRows =
5×3 table
Time (HH:mm:ss:) Surface Temperature (°C) Bottom Temperature (°C)
________________ ________________________ _______________________
00:00:00 9.5343 9.4499
00:05:02 9.5223 9.4494
00:09:56 9.5344 9.4493
00:14:58 9.5315 9.4489
00:20:00 9.5251 9.448
.

추가 답변 (1개)

Cris LaPierre
Cris LaPierre 2020년 12월 6일
I thought of 2 ways to do it.
1. Treat the times in first column as durations in days, and add it to Jan 0, 2014
opts = detectImportOptions("ccs_temperatures2014.xlsx");
opts.VariableNames = ["Time","sTemp","nbTemp"];
opts = setvartype(opts,"Time","duration");
opts = setvaropts(opts,"Time","DurationFormat","d");
data = readtable("ccs_temperatures2014.xlsx",opts);
data.Time = data.Time + datetime(2014,01,0);
data.Time.Format = "HH:mm:ss"
data = 80306x3 table
Time sTemp nbTemp ________ ______ ______ 00:00:00 9.5343 9.4499 00:05:02 9.5223 9.4494 00:09:56 9.5344 9.4493 00:14:58 9.5315 9.4489 00:20:00 9.5251 9.448 00:25:03 9.525 9.4478 00:29:57 9.5271 9.4472 00:34:59 9.5248 9.4468 00:40:01 9.5166 9.4465 00:44:55 9.5209 9.4446 00:49:58 9.516 9.4448 00:55:00 9.5177 9.4444 01:00:02 9.5149 9.4423 01:04:56 9.513 9.442 01:09:59 9.5042 9.4415 01:15:01 9.5081 9.4404
2. Import using the 'excel1904' format, and add the appropriate number of years to get it to the years of the file.
opts = detectImportOptions("ccs_temperatures2014.xlsx");
opts.VariableNames = ["Time","sTemp","nbTemp"];
data = readtable("ccs_temperatures2014.xlsx",opts);
data.Time = datetime(data.Time,'ConvertFrom',"excel1904") + calyears(110);
data.Time.Format = "HH:mm:ss"
data = 80306x3 table
Time sTemp nbTemp ________ ______ ______ 00:00:00 9.5343 9.4499 00:05:02 9.5223 9.4494 00:09:56 9.5344 9.4493 00:14:58 9.5315 9.4489 00:20:00 9.5251 9.448 00:25:03 9.525 9.4478 00:29:57 9.5271 9.4472 00:34:59 9.5248 9.4468 00:40:01 9.5166 9.4465 00:44:55 9.5209 9.4446 00:49:58 9.516 9.4448 00:55:00 9.5177 9.4444 01:00:02 9.5149 9.4423 01:04:56 9.513 9.442 01:09:59 9.5042 9.4415 01:15:01 9.5081 9.4404

카테고리

Help CenterFile Exchange에서 Spreadsheets에 대해 자세히 알아보기

Community Treasure Hunt

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

Start Hunting!

Translated by