Plotting Date Time data being read from an excel table

I'm trying to read an excel file where the format of the date/time data is: '01.10. 07:30' ('dd.mm. HH:MM')

I can read the raw text data into MATLAB using:

[num,text,both] = xlsread(filename,sheet,'B:B');

But I then try to convert this into date number format using:

DateTime = datenum(date,'dd.mm. HH:MM');

However I'm getting the following error:

Error using datenum (line 188)

DATENUM failed.

Error in WeatehrInfoGraphs (line 9)

DateTime = datenum(date,'dd.mm. HH:MM');

Caused by:

Error using dtstr2dtnummx

Failed to convert from text to date number.

[Ieuan Price Davies]

CAn anyone help explain how to fix this? I think it may be to do with formatting but as its an unusual way of formatting date/time I'm having some issues

Answer 1

[Cris LaPierre]

Use readtable instead of xlsread.

Import your dates as datetimes. Avoid using datenum.

Then, when you plot, your datetimes will appear in your axis. If you need to adjust the format, use the xtickformat function.

[Ieuan Price Davies]

Thanks!

How would you format the datetime argument?

Table = readtable(filename, 'Sheet',sheet, 'Range', 'B:B');
DateTime = datetime(Table,'InputFormat','MM.dd. hh:mm');

This is still giving me an error:

Error using datetime (line 661)

Input data must be a numeric array, a string array, a cell array containing character vectors, or a char matrix.

Error in WeatehrInfoGraphs (line 9)

DateTime = datetime(Clock,'InputFormat','MM.dd. hh:mm');

YOur help is much aprreciated!

[Cris LaPierre]

Can you attach a sample dataset? You can use the paperclip icon to do so. Readtable is pretty good at autodetecting data type, so I might do something like this:

opts = detectImportOptions(filename);
data = readtable(filename,opts);

You can also use the import tool to do this interactively. You can see an example here.

You can find an example of importing times from a text file here.

[Ieuan Price Davies]

This is the dataset I'm using, it's the weather conditions in zanzibar for the first week of the year. The column I'm trying to format is Column B. Thanks, I will try the above suggestions now.

[Cris LaPierre]

Ok, here's how I would load the spreadsheet.

opts = detectImportOptions("ZanzibarConditions.xlsx");
opts = setvartype(opts,"Date","datetime");
opts = setvaropts(opts,"Date",'InputFormat','MM.dd. HH:mm');
% By default, variable names are the column headers. That can result in a warning (see below)
% These can be changed using the syntax below (uncomment & provide name for each column).
% opts.VariableNames = ["Hour","Date","NDI","DIAS",...];

data = readtable("ZanzibarConditions.xlsx",opts)

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

data = 168x10 table
    Hour        Date        NormalDirectIrradiation_W_m___Bn_    DiffuseIrradiation_AnnualSum_W_m___Dh_    GlobalIrradiation_AnnualSum_W_m___Gh_    AirHumidity____Hrel_    LongWavelengthIrradiation_W_m___Lh_    OutdoorTemperature24_h_mean__C__Tamb24_    WindSpeed_m_s__Vwnd_    AverageOutdoorTemperature__C__Tamb_
    ____    ____________    _________________________________    ______________________________________    _____________________________________    ____________________    ___________________________________    _______________________________________    ____________________    ___________________________________

      0     01.01. 00:00                     0                                       0                                        0                               88                            435                                       28                              3.7                            27.2                
      1     01.01. 01:00                     0                                       0                                        0                               92                            433                                     28.1                              3.2                            26.4                
      2     01.01. 02:00                     0                                       0                                        0                             96.2                            430                                     28.2                              4.8                            25.5                
      3     01.01. 03:00                     0                                       0                                        0                             92.8                            432                                     28.2                              5.5                            26.2                
      4     01.01. 04:00                     0                                       0                                        0                             97.6                            433                                     28.3                              5.1                            25.7                
      5     01.01. 05:00                     0                                       0                                        0                             94.9                            433                                     28.3                                4                              26                
      6     01.01. 06:00                     0                                    12.9                                     12.9                             93.7                            432                                     28.4                                4                              26                
      7     01.01. 07:00                   749                                    51.3                                      259                             88.4                            399                                     28.5                              4.2                            26.9                
      8     01.01. 08:00                   774                                     107                                      498                             79.5                            393                                     28.5                              4.2                            27.8                
      9     01.01. 09:00                   484                                     277                                      614                             75.5                            398                                     28.5                              4.2                            28.5                
     10     01.01. 10:00                   844                                     265                                      972                             70.4                            385                                     28.5                              4.4                            29.4                
     11     01.01. 11:00                   272                                     480                                      732                             68.3                            403                                     28.5                              4.8                            29.7                
     12     01.01. 12:00                   403                                     434                                      819                             66.4                            399                                     28.5                              4.6                            29.9                
     13     01.01. 13:00                   234                                     454                                      669                             65.4                            404                                     28.4                              4.8                              30                
     14     01.01. 14:00                   207                                     412                                      582                             64.9                            404                                     28.3                              4.6                            29.9                
     15     01.01. 15:00                  19.3                                     238                                      251                             66.2                            420                                     28.2                              5.9                            29.3                

With your data in a table, you can access the individual variables using dot notation.

data.Date

ans = 168×1 datetime array
   01.01. 00:00
   01.01. 01:00
   01.01. 02:00
   01.01. 03:00
   01.01. 04:00
   01.01. 05:00
   01.01. 06:00
   01.01. 07:00
   01.01. 08:00
   01.01. 09:00
   01.01. 10:00
   01.01. 11:00
   01.01. 12:00
   01.01. 13:00
   01.01. 14:00
   01.01. 15:00
   01.01. 16:00
   01.01. 17:00
   01.01. 18:00
   01.01. 19:00
   01.01. 20:00
   01.01. 21:00
   01.01. 22:00
   01.01. 23:00
   02.01. 00:00
   02.01. 01:00
   02.01. 02:00
   02.01. 03:00
   02.01. 04:00
   02.01. 05:00

For more information, visit Accessing Data in Tables.

[Peter Perkins]

In addition to what Cris sys, you might find it convenient to convert that table to a timetable using table2timetable.