MATLAB Answers

0

Reading the exact time and date as in excel sheet !

Asked by chamant swarup on 22 Jan 2019
Latest activity Commented on by Peter Perkins
on 24 Jan 2019
Time that is being displayed in workspace editor !!
0,0104166666666667
0,0208333333333333
0,0312500000000000
0,0416666666666667
0,052083333333333
Original time stamp !!
0:15
0:30
0:45
1:00
1:15
[Houseload,text,rawdata] = xlsread('Haushalt-Lastprofil.xls','Profil');
time = Houseload (:,1);
what is the next code line to be implemented ! here are few things i implemented based on few examples discussed in the forum but was not sucessful .
1) timecolomn = datestr(datenum(time(:,1)), 'hh:mm');
2) datestr(Houseload,'mm/dd/yyyy hh:mm:ss')
I would also like to know the code to read if the following format it existed !
01.01.2017 01:15:00
thanks !

  5 Comments

Answer moved here:
doc readtable
i use matlab 2007 so this command does not work in older versions according to few similar questions in the forum but still i tried and was not successful !
In that case you should upload your original excel file to experiment so that others can give you a precise result , next time when you ask a question make sure you fill "Products" and "Release" field so that this type of confusions can be avoided.
Please find the attached excel file asked for !!
Thanks !

Sign in to comment.

Products


Release

R2007b

2 Answers

Answer by per isakson
on 23 Jan 2019
Edited by per isakson
on 23 Jan 2019
 Accepted Answer

"based on few examples discussed in the forum" The Matlab documentation is a much more reliable when it comes to the behavior of specific functions. Yes, you want to use the function datestr(). Thus see datestr, Convert date and time to string format and notice that
Symbolic Identifier Description
HH Hour in two digits
MM Minute in two digits
That is capital letters "H" and "M". Run this code
%%
[Houseload,text,rawdata] = xlsread('Haushalt-Lastprofil.xls','Profil');
time = Houseload (:,1);
%%
datestr( time, 'HH:MM' )
outputs
ans =
96×5 char array
'00:15'
'00:30'
'00:45'
'01:00'
'01:15'
'01:30'
...
I use R2018b. However, datestr() was Introduced before R2006a and should work with R2007. Possibly, your output will include default values for year, month and day.
"I would also like to know [...]" It does and the answer is in the documentation datestr()

  2 Comments

Thanks for the help !! even i switched to the latest version today !
Now to plot the time versus the data i am looking for ,
why the following doesnt work? it says
Error in loadprofiles (line 6)
plot(ans,TotconYEAR);
pls find the excel file if needed below
[ Hloadprofile,text,raw ] = xlsread('Householdload.xls','Profil');
% time = Hloadprofile(:,1);
datestr( Hloadprofile ,'HH:MM');
TotconYEAR = Hloadprofile(:,11);
plot(ans,TotconYEAR);
With R2018b I get
Error using plot
Invalid first data argument.
Error in Untitled (line 6)
plot(ans,TotconYEAR);
I see two problems with your code:
  1. the use of ans, Most recent answer in a script/function. That is asking for trouble. (It is used in Cody to trick the size calculator, by avoiding creation of one variable.)
  2. the value of ans in your case is a character array. That makes plot() throw an error.
Try
plot( Hloadprofile(:,1), TotconYEAR );

Sign in to comment.


Answer by Peter Perkins
on 23 Jan 2019

chamant, you will be much better off using readtable, and durations, if you have anythign like a recent version of MATLAB.

  3 Comments

Yes but i had trouble using the readtable function too ! Take a look at the following code which showed errors !
Hloadprofile1 = readtable('Householdload.xls','sheet','Profil');
It still read the time array as string !! and i am finding it difficult to plot the data !!
Yes, but don't forget
T = readtable( _____, 'DatetimeType', 'exceldatenum' )
readtable has had a lot of functionality added over the last couple years, so check the documentation for the version you are running. Notably, recent versions of readtable work with detectimportoptions. If you have a recent enough version of MATLAB, that's the way to go. If not, still I would recommend readtable, and if the dates come in as strings, convert them to datetime.

Sign in to comment.