Reading the exact time and date as in excel sheet !

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 !

[Jan]

What are the problems with the two shown commands? Does this work:

timecolomn = datestr(time(:,1), 'hh:mm')

Where does "the following format exist"? Is this a string or a date displayed in Excel?

[chamant swarup]

I just got the format from random query posted in the forum ! The above code returned something like this in the array editor !!

timecolomn =

hh:01

hh:01

hh:01

hh:01

hh:01

hh:01

hh:01

hh:01

hh:01

hh:01

hh:01

So it did not work !!

Can you give me an alternative for the data to be read as asked ?

[madhan ravi]

Answer moved here:

madhan ravi :

doc readtable

chamant swarup :

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 !

[madhan ravi]

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.

[chamant swarup]

Please find the attached excel file asked for !!

Thanks !

Answer 1

[per isakson]

"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()

Finally, regarding Excel dates see Converts Excel serial date to day month year and back

[chamant swarup]

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);

[per isakson]

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 );

Answer 2

[Peter Perkins]

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

[chamant swarup]

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 !!

[per isakson]

Yes, but don't forget

T = readtable( _____, 'DatetimeType', 'exceldatenum' )

[Peter Perkins]

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.