MATLAB Answers

Datevector conversion from Excel to Matlab

5 views (last 30 days)
I'm trying to convert an long Excel-format date array and the conversion I normally use is returning incorrect values. The date starts at 3/1/1942.
The format in excel is: 3/1/1942 1:00:00 AM
Below are my lines to import and convert to MATLAB Date Vector and Datenum. I've cleaned up the dataset to only include the date vector from excel. So, ignore the txt, and raw variables.
%% Define file location, and load into the workspace.
stafiles_WGA = 'datevec_raw.xlsx';
% Read the data.
[num_WGA, txt_WGA, raw_WGA] = xlsread(stafiles_WGA); % Creates three variables. A number (double), raw (cell), and txt (cell).
% Note that the 'txt' and 'raw' variables contain the decriptions of the columns.
% Rename the numerical matrix and remove the upper row that originally contained the data headers.
sta_WGA = num_WGA;
datevec_WGA = datetime(sta_WGA,'ConvertFrom','excel');
date_num_WGA = datenum(datevec_WGA);
Any idea what I'm not catching?
Thanks

  2 Comments

Walter Roberson
Walter Roberson on 21 Sep 2020
Is there a reason you are not using readtable() ?
Eric Escoto
Eric Escoto on 21 Sep 2020
Only that I’ve just used this method for all my scripts to date with no issues regarding the date conversions. If there’s a better way I’m fine to try it.

Sign in to comment.

Accepted Answer

Cris LaPierre
Cris LaPierre on 21 Sep 2020
I would read them in this way:
data = readtable('datevec_raw.xlsx','ReadVariableNames',false);

  14 Comments

Cris LaPierre
Cris LaPierre on 21 Sep 2020
Where are you expecting to see them? As I said, the coumn headers become the VariableNames (e.g. stafiles_WGA.DATE).
stafiles_WGA =
Station_Name ELEVATION LATITUDE LONGITUDE DATE HOURLYVISIBILITY AIRTEMPC HOURLYDRYBULBTEMPF HOURLYDRYBULBTEMPC HOURLYWETBULBTEMPF HOURLYWETBULBTEMPC HOURLYDewPointTempF HOURLYDewPointTempC HOURLYRelativeHumidity HOURLYWindSpeed HOURLYWindDirection HOURLYPrecip_in HOURLYPrecip_mm HOURLYAltimeterSetting
______________ _________ ________ _________ __________ ________________ ________ __________________ __________________ __________________ __________________ ___________________ ___________________ ______________________ _______________ ___________________ _______________ _______________ ______________________
{'WBAN:23104'} 421.2 33.3 -111.67 7.0936e+05 99.42 {'NAN'} 39 4.1 {'NAN'} {'NAN'} 37 3 93 1 360 {'NAN'} {'NAN'} {'NAN'}
{'WBAN:23104'} 421.2 33.3 -111.67 7.0936e+05 99.42 {'NAN'} 37 3 {'NAN'} {'NAN'} 35 1.9 93 0 0 {'NAN'} {'NAN'} {'NAN'}
{'WBAN:23104'} 421.2 33.3 -111.67 7.0936e+05 99.42 {'NAN'} 36 2.4 {'NAN'} {'NAN'} 34 1.3 93 0 0 {'NAN'} {'NAN'} {'NAN'}
{'WBAN:23104'} 421.2 33.3 -111.67 7.0936e+05 99.42 {'NAN'} 36 2.4 {'NAN'} {'NAN'} 34 1.3 93 0 0 {'NAN'} {'NAN'} {'NAN'}
{'WBAN:23104'} 421.2 33.3 -111.67 7.0936e+05 99.42 {'NAN'} 35 1.9 {'NAN'} {'NAN'} 33 0.8 93 0 0 {'NAN'} {'NAN'} {'NAN'}
{'WBAN:23104'} 421.2 33.3 -111.67 7.0936e+05 99.42 {'NAN'} 34 1.3 {'NAN'} {'NAN'} 32 0.2 92 0 0 {'NAN'} {'NAN'} {'NAN'}
{'WBAN:23104'} 421.2 33.3 -111.67 7.0936e+05 99.42 {'NAN'} 34 1.3 {'NAN'} {'NAN'} 32 0.2 92 0 0 {'NAN'} {'NAN'} {'NAN'}
{'WBAN:23104'} 421.2 33.3 -111.67 7.0936e+05 99.42 {'NAN'} 42 5.8 {'NAN'} {'NAN'} 35 1.9 76 0 0 {'NAN'} {'NAN'} {'NAN'}
{'WBAN:23104'} 421.2 33.3 -111.67 7.0936e+05 99.42 {'NAN'} 51 10.7 {'NAN'} {'NAN'} 39 4.1 64 0 0 {'NAN'} {'NAN'} {'NAN'}
{'WBAN:23104'} 421.2 33.3 -111.67 7.0936e+05 99.42 {'NAN'} 54 12.4 {'NAN'} {'NAN'} 38 3.5 55 0 0 {'NAN'} {'NAN'} {'NAN'}
...
To see the property values, use the following code:
stafiles_WGA.Properties.VariableNames
ans = 1×19 cell
'Station_Name' 'ELEVATION' 'LATITUDE' 'LONGITUDE' 'DATE' 'HOURLYVISIBILITY' 'AIRTEMPC' 'HOURLYDRYBULBTEMPF' 'HOURLYDRYBULBTEMPC' 'HOURLYWETBULBTEMPF' 'HOURLYWETBULBTEMPC' 'HOURLYDewPointTempF' 'HOURLYDewPointTempC' 'HOURLYRelativeHumidity' 'HOURLYWindSpeed' 'HOURLYWindDirection' 'HOURLYPrecip_in' 'HOURLYPrecip_mm' 'HOURLYAltimeterSetting'
Eric Escoto
Eric Escoto on 21 Sep 2020
Oh, I found the issue way back when I used this line that was provided.
data = readtable('datevec_raw.xlsx','ReadVariableNames',false);
I just changed it and all is well.
stafiles_WGA = readtable('WGA_stationdata_corrected.xlsx','ReadVariableNames',true);
Thanks!
Cris LaPierre
Cris LaPierre on 21 Sep 2020
Yup, or drop the "ReadVariableNames" setting completely, like I showed in my previous response (true is the default setting, so doesn't need to be set explicitely).

Sign in to comment.

More Answers (0)

Tags

Community Treasure Hunt

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

Start Hunting!