timetable for average value

Hi everyone

I want to create a timetable for my data imported from excel as numerical matrix, the data has the date and time column. I try by creating the datetime in excel by concatenation, But I get an error. below is the code,

dateSamples = ausdata(:,1); 
dateSamples = x2mdate(dateSamples); 
timeSamples = datestr(ausdata(:,2),'HH:MM:SS')%

Here I don't how to do build the datetime.

datetimeSample =
load = ausdata(:,9)
TT = timetable(datetimeSamples,load);
daily= retime(TT,'daily','mean');
weekly = retime(TT, 'monthly', 'mean')

Please assist me....I attached the excel file

Answer 1

[Adam Danz]

x2mdate() converts from excel serial date number to Matlab serial date number. An optional 3rd input allows you to convert the excel date to Matlab's datetime format.

MATLABDate = x2mdate(dateSamples, 0,'datetime')

---[Update]---

Use readtable() to read in the excel table.

opts = spreadsheetImportOptions('NumVariables',9, ...
    'VariableTypes', {'char','char','datetime','double','double','double','double','double','double'},...
    'VariableNamesRange', 'A1', 'DataRange', 'A2'); 
T = readtable('ausdata.xlsx', opts);

% Look at first few rows
head(T)
  
%  8×9 table
%          Date              Hour        Var3    DryBulb    DewPnt    WetBulb    Humidity    ElecPrice    SYSLoad
%     _______________    ____________    ____    _______    ______    _______    ________    _________    _______
%     {'01-Jan-2006'}    {'0.020833'}    NaT      23.9      21.65       22.4       87.5        19.67      8013.3 
%     {'01-Jan-2006'}    {'0.041667'}    NaT      23.9       21.7       22.4         88        18.56      7726.9 
%     {'01-Jan-2006'}    {'0.0625'  }    NaT      23.8      21.65      22.35         88        19.09      7372.9 
%     {'01-Jan-2006'}    {'0.083333'}    NaT      23.7       21.6       22.3         88         17.4      7071.8 
%     {'01-Jan-2006'}    {'0.10417' }    NaT      23.7       21.6       22.3         88           17      6865.4 
%     {'01-Jan-2006'}    {'0.125'   }    NaT      23.7       21.6       22.3         88           17      6685.9 
%     {'01-Jan-2006'}    {'0.14583' }    NaT      23.6      21.65       22.3         89           17      6548.6 
%     {'01-Jan-2006'}    {'0.16667' }    NaT      23.5       21.7       22.3         90        16.92      6487.8 

The NaT values in column 3 in my example may be due to a difference in the version of Excel I'm currently using.

If you're also getting NaT values in column 3 you can recreate the datetime values using this,

T.Properties.VariableNames{3} = 'datetime'; 
T.datetime = dateshift(T.Date,'start','day') + T.Hour;

% head(T)
%        Date          Hour             datetime          DryBulb    DewPnt    WetBulb    Humidity    ElecPrice    SYSLoad
%     ___________    _________    ____________________    _______    ______    _______    ________    _________    _______
%     01-Jan-2006     1800 sec    01-Jan-2006 00:30:00     23.9      21.65       22.4       87.5        19.67      8013.3 
%     01-Jan-2006     3600 sec    01-Jan-2006 01:00:00     23.9       21.7       22.4         88        18.56      7726.9 
%     01-Jan-2006     5400 sec    01-Jan-2006 01:30:00     23.8      21.65      22.35         88        19.09      7372.9 
%     01-Jan-2006     7200 sec    01-Jan-2006 01:59:59     23.7       21.6       22.3         88         17.4      7071.8 
%     01-Jan-2006     9000 sec    01-Jan-2006 02:30:00     23.7       21.6       22.3         88           17      6865.4 
%     01-Jan-2006    10800 sec    01-Jan-2006 03:00:00     23.7       21.6       22.3         88           17      6685.9 
%     01-Jan-2006    12600 sec    01-Jan-2006 03:29:59     23.6      21.65       22.3         89           17      6548.6 
%     01-Jan-2006    14400 sec    01-Jan-2006 03:59:59     23.5       21.7       22.3         90        16.92      6487.8 

[karim bio gassi]

Thanks for your answer.

I use the method x2mdate(dateSamples, 0,'datetime'), it works for the date. Now how should I concatenate with the timeSample.

I try to apply the same method x2mdate(dateSamples, 0,'datetime') to the third column of excel data where I concatenate the date and the time, matlab create the datetime data type filles with

'NaT'

'NaT'

'NaT'

'NaT'

'NaT'

[Adam Danz]

It looks like you're combining the date and time in column 3 of your excel file. Why not just read in the datetime values from column C?

BTW, what are you using to read in the excel data? Could you share that part of your code if you need more help?

[karim bio gassi]

You are right, I combine date and time for get column by using concatenate in excel. Iam a beginner, I don't know how to read in the datetime values from column. Please share how to do that process.

To read the excel file I use the matlab import data. At this point, I share the lines of code I have in my first question

thanks for your help

[Adam Danz]

See my updated answer.

[karim bio gassi]

I saw your update. You give the full answer to problem. Thanks very much and be safe. I learnt another new thing. Matlab is great.

[Adam Danz]

Glad I could help!