How to change a variable to a time variable?

35 views (last 30 days)
Angelavtc
Angelavtc on 8 Sep 2020
Edited: Star Strider on 8 Sep 2020
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!
  2 Comments
Angelavtc
Angelavtc on 8 Sep 2020
@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.

Sign in to comment.

Accepted Answer

Star Strider
Star Strider on 8 Sep 2020
Edited: Star Strider on 8 Sep 2020
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
.

More Answers (1)

Steven Lord
Steven Lord on 8 Sep 2020
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

Community Treasure Hunt

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

Start Hunting!

Translated by