How to change a variable to a time variable?

I have the following excel file (ejemplo.xlsx) where the first column is date (format yyyy-MM-dd) , the second start hour and the third is last hour. How can I tell Matlab that the second and third columns are datetime variables? I tried the following code but is not working.

Ej = readtable('Ejemplo.xlsx'); 
ej  = table2timetable(Ej); 
ej= datetime(ej.StartTime,'Format','HH:mm')

Thanks in advance!

[Dana]

"It's not working" is about the vaguest description of a problem I can conceive of. If you put some more effort into your question you might be able to get a useful answer.

[Angelavtc]

@Dana Ahhh common thats why I am provinding a code and an excel file, sometimes I dont undestand people like you, who think just because you know matlab you can critize and destroy people who are asking for help. If I am asking is because I am learning. If you dont want to help or you think this questions is not good enough just continue your life. Why you waste your time even answering this? Leave others help me and continue.

Answer 1

[Star Strider]

One approach:

Ej = readtable('Ejemplo.xlsx');
Ej.StartTime = Ej{:,1} + days(Ej{:,2});
Ej.EndTime = Ej{:,1} + days(Ej{:,3});

with:

FirstFiveRows = Ej(1:5,:)

producing:

FirstFiveRows =

  5×5 table

       Date             StartTime                EndTime           ForecastInMW    ActualInMW
    ___________    ____________________    ____________________    ____________    __________

    01-Sep-2010    01-Sep-2010 00:00:00    01-Sep-2010 00:15:00       1037.6          983.3  
    01-Sep-2010    01-Sep-2010 00:15:00    01-Sep-2010 00:30:00       1071.2         1177.7  
    01-Sep-2010    01-Sep-2010 00:30:00    01-Sep-2010 00:45:00       1098.5         1397.7  
    01-Sep-2010    01-Sep-2010 00:45:00    01-Sep-2010 01:00:00       1339.1         1414.4  
    01-Sep-2010    01-Sep-2010 01:00:00    01-Sep-2010 01:15:00       1360.4         1585.1      

EDIT — (8 Sep 2020 at 16:02)

To use my code with 'PreserveVariableNames',true (and avoiding the Warning that appears otherwise), my code changes slightly to:

Ej = readtable('Ejemplo.xlsx', 'PreserveVariableNames',1);
Ej.('Start Time') = Ej{:,1} + days(Ej{:,2});
Ej.('End Time') = Ej{:,1} + days(Ej{:,3});

with:

    FirstFiveRows = Ej(1:5,:)

producing:

FirstFiveRows =

  5×5 table

       Date             Start Time               End Time          Forecast in MW    Actual in MW
    ___________    ____________________    ____________________    ______________    ____________

    01-Sep-2010    01-Sep-2010 00:00:00    01-Sep-2010 00:15:00        1037.6            983.3   
    01-Sep-2010    01-Sep-2010 00:15:00    01-Sep-2010 00:30:00        1071.2           1177.7   
    01-Sep-2010    01-Sep-2010 00:30:00    01-Sep-2010 00:45:00        1098.5           1397.7   
    01-Sep-2010    01-Sep-2010 00:45:00    01-Sep-2010 01:00:00        1339.1           1414.4   
    01-Sep-2010    01-Sep-2010 01:00:00    01-Sep-2010 01:15:00        1360.4           1585.1   

.

Answer 2

[Steven Lord]

Those columns in your spreadsheet are not what I would call a datetime. They have a time component, yes, but not a date component. I'd read them in as duration arrays and add them to the datetime from the first column to get a date-and-time.

>> t = detectImportOptions(thefile);
>> t.VariableTypes{2} = 'duration';
>> t.VariableTypes{3} = 'duration';
>> E1 = readtable(thefile, t);
>> head(E1)
ans =

  8×5 table

       Date        StartTime    EndTime     ForecastInMW    ActualInMW
    ___________    _________    ________    ____________    __________

    01-Sep-2010       0 sec      900 sec       1037.6          983.3  
    01-Sep-2010     900 sec     1800 sec       1071.2         1177.7  
    01-Sep-2010    1800 sec     2700 sec       1098.5         1397.7  
    01-Sep-2010    2700 sec     3600 sec       1339.1         1414.4  
    01-Sep-2010    3600 sec     4500 sec       1360.4         1585.1  
    01-Sep-2010    4500 sec     5400 sec       1386.7         1811.7  
    01-Sep-2010    5400 sec     6300 sec       1408.7         1969.5  
    01-Sep-2010    6300 sec     7200 sec       1531.2         1810.3  

>> y = E1.Date(1:5) + E1.StartTime(1:5)

y = 

  5×1 datetime array

   01-Sep-2010 00:00:00
   01-Sep-2010 00:15:00
   01-Sep-2010 00:30:00
   01-Sep-2010 00:45:00
   01-Sep-2010 01:00:00