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
.
