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

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!

Answer 1

[Star Strider]

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  

.

Answer 2

[Cris LaPierre]

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