Converting date range from numeric to date format in mat file

I have a mat file in which I store the data to use for analysis when needed, I wanted to past a data from excel to the mat file

This is the data I need to copy to the mat file from excel

date	            Inflows	           HPI
6/30/1985'	-738.4928374	-1.376979203
9/30/1985'	-149.8324882	-1.318316639
12/30/1985'	-389.9021275	-1.404933598
3/30/1986'	-1250.503701	-0.137879304
6/30/1986'	-944.0048226	-3.060762411
9/30/1986'	-64.68902275	0.169427825
12/30/1986'	-278.1197318	0.049207493
3/30/1987'	-507.793333	4.174160222

However, when I past the data, the date column is taken as numeric as seen below

'date'	         'Inflows'	               'HPI'

0.000151133501259446	-149.832488200000	-1.31831663900000
0.000201511335012594	-389.902127500000	-1.40493359800000
5.03524672708963e-05	-1250.50370100000	-0.137879304000000
0.000100704934541793	-944.004822600000	-3.06076241100000
0.000151057401812689	-64.6890227500000	0.169427825000000
0.000201409869083585	-278.119731800000	0.0492074930000000
5.03271263210871e-05	-507.793333000000	4.17416022200000
0.000100654252642174	-317.350932300000	3.98837463000000
0.000150981378963261	-289.777479600000	2.87872744700000

Is there any way to convert the date column from numeric to date format

[dpb]

Where does the sequence start to generate the data in the beginning? Need to fix the sequence of creating it to use the correct form at that point and be consistent. In Matlab, use the builtin datetime class for time data variables and a table containing the variables will then each have the proper storage format when SAVE/LOAD from a .mat file.

How you would have gotten such as above specifically isn't clear to me--those aren't Excel nor old ML date numbers with such small exponents so you've done something unusual somewhere...

ADDENDUM

I don't know how you managed it and we can only guess since you don't show us any code, but the answer you have for the date column is actually the result of interpreting the date string as an expression--

>> fprintf('%.14f\n',12/30/1986)
0.00020140986908
>> 

matches one of the values that match between the two datasets above.

Again, how you did that is a mystery, but that's the root of your problem.

[Ameer Fahmi]

Thanks for your comment, I have solved this problem by importing my data (SAdata) into Matlab then pass it to the GC mat file by doing this

GC.y=SAdata

GC = 

  struct with fields:

    q: {142×5 cell}
    m: {424×5 cell}
    y: {136×3 cell}

The GC mat file is structure file with three fields as seen above. This is how my data looks after passing it to the mat file

    "date"	  "Inflows"	             "HPI"
"6/30/1985"	-738.492837412390	-1.37697920341393
"9/30/1985"	-149.832488184277	-1.31831663921641
"12/31/1985"	-389.902127478935	-1.40493359803057
"3/31/1986"	-1250.50370119007	-0.137879304433611
"6/30/1986"	-944.004822635733	-3.06076241123973
"9/30/1986"	-64.6890227458389	0.169427824516215
"12/31/1986"	-278.119731812238	0.0492074928011591
"3/31/1987"	-507.793332986046	4.17416022215573
"6/30/1987"	-317.350932335587	3.98837462979420
"9/30/1987"	-289.777479612941	2.87872744682405

However, I tired to seperate the data column by doing this

data=GC.y;
date=datenum(cell2mat(data(2:end,1)),'mm/dd/yyyy');

But I got this error

Error using cell2mat (line 52)
CELL2MAT does not support cell arrays containing cell arrays or objects.

how can I solve this problem?

[Ameer Fahmi]

I think the problem is that the date column is a charater, that is why the function datenum did not work, so the real problem here is how to convert date column from character to numeric, how can I do that?

[dpb]

See the earlier addendum to my previous comment...

The answer is to read the data as a date %D field when first reading the data...but you won't tell us where the data is coming from and in what format nor show us how you're trying to read it...

I'll go back to the original question -- where is the beginning of the process that creates the data you're trying to read and how are you trying to read it and what is the end result needed?

How did you get the listing in the above? How is the data stored there, but more importantly, where did it come from--let's go back a step and get the right encoding at that point instead of having to do yet another cast() operation.

[Ameer Fahmi]

The GC file is part of a mat file used in a code of time-varying causality, in this code there is mat file called Slope_Macro.mat in which there is structure file called GC, the latter has 2 fields. By adding my data (SAdata) it will be three fields. The function named RGC_Macro_Finance_monthly.m in the code loads the data from the GC file in the mat file to estimate the the recusive evolving granger causality. This is the formula used in the code of this function to load the data

load('Slope_Macro.mat');
data=GC.m;

date=datenum(cell2mat(data(2:end,1)),'dd/mm/yyyy');
Inf=cell2mat(data(2:end,2));
gip=cell2mat(data(2:end,3));
frate=cell2mat(data(2:end,4));
spread=cell2mat(data(2:end,5));

y=[gip Inf frate spread];

This is why I needed to pass my data to the GC file. To do that I first tried to past my data directly from excel to the GC file in Matlab as I referred to in my first post but the date column was taken in strange format as you said. Then I managed to get around this problem by importing my data (SAdata) into Matlab which originally looks like this in excel

date	          Inflows	     HPI
6/30/1985	-738.4928374	-1.376979203
9/30/1985	-149.8324882	-1.318316639
12/31/1985	-389.9021275	-1.404933598
3/31/1986	-1250.503701	-0.137879304
6/30/1986	-944.0048226	-3.060762411
9/30/1986	-64.68902275	0.169427825
12/31/1986	-278.1197318	0.049207493
3/31/1987	-507.793333	4.174160222
6/30/1987	-317.3509323	3.98837463
9/30/1987	-289.7774796	2.878727447
12/31/1987	-530.6950067	5.050070933
3/31/1988	-155.5525937	3.608376136
6/30/1988	-14.48986615	2.223192278

After importing my data into matlab in array format, I passed it to the GC file by doing this

GC.y=SAdata
GC = 
  struct with fields:
    q: {142×5 cell}
    m: {424×5 cell}
    y: {136×3 cell}

But this data in the filed y need to be sepereated again, because the funaction I referred to previously use each column of the data seperately. So I took it again and named it as data by doing this

   data=GC.y; 

Then tried to seperate date column by doing this

   date=datenum(cell2mat(data(2:end,1)),'mm/dd/yyyy');

The other variables in y field were easilly seperated. The problem is in the dates column whic I couldn't seperate. I tried datetime and datenum funtions but nothing worked. I always had the errors I referred to in my previous comments.

[dpb]

I'm still lost as to who's the first step in this process -- you talk of multiple files and .mat and Excel, but the path between is murky at best.

While not your fault and not casting blame, that English isn't first language doesn't help; overall your usage is quite good in fact, but some details just aren't coming through (to me, anyway).

Let me try another way--Are there two separate data sources, not just one--one being from Excel and the other this .mat file and you're trying to merge data from each, maybe?

If so, attach a sample file of each particularly the one that contains the date data that is being misinterpreted and the code you used to read it that resulted in the above numeric answer that is the algebraic solution to the text string, not the interpretation as a date.

If that is in Excel, only by seeing the actual file can we see just what is actually in the file and how it is stored internally there. Apparently it is not a date; Matlab usually has no issue in importing Excel dates.

[Ameer Fahmi]

The first step in which I got that strange format of dates column was done by pasting the data from excel into an existing field in the GC file, as I referred to previously the structure GC file had two fields which are the original data of the code. I opened the GC.m field and I tried to past my data from excel to the field directly, the outcome of the process is what you see in the first post. But this is no longer an issue for me because I imported my data from excel into matlab then I passed my data into the GC file under the name GC.y as I explained.

Regarding the path of the file, the GC file is inside a mat file named "Slope_Macro.mat". The GC file is structure file with three fields contains the data that will be used in the code.

The only source of my data is excel, I imported my data into excel using the array formats. then passed it to the GC file. the problem is that the function cell2mat does not work on the dates column when I try to seperate the variables of my data, but it works with the other variables with no problem.

Hope this will make it more clear, I'm sorry that I make it hard for you to understand, but the process how data is handled in the code I'm using is complicated. I don't know why the writer of the code didn't use simple loading from excel or csv files instead of using this long path of mat file then structure file in the mat file and lastly creating fields in the structure file

Anyway, I have attached my data

[dpb]

Is the code an m-file? Shouldn't be difficult to modify to simplify at least the interface if not the internals.

As for the specific question, the "paste" operation undoubtedly is what caused the problem with the date being interpreted as an expression -- when did that, lost the type as being a date string representation and the slash separators looked to the interpreter like math operators and that's what it did--divided the three numbers and stored the result. That mystery of how that could have occurred now seems understandable. The solution is "don't do that!" :)

We still don't have the actual code you used, but to read the Excel sheet, simply

SAData=readtable('SAData.xlsx');

returns

>> SAData=readtable('SAdata.xlsx');
>> whos SAData
  Name          Size            Bytes  Class    Attributes

  SAData      135x3              4588  table              

>> SAData(1:5,:)
ans =
  5×3 table
       date       Inflows      HPI   
    __________    _______    ________
    6/30/1985     -738.49      -1.377
    9/30/1985     -149.83     -1.3183
    12/31/1985     -389.9     -1.4049
    3/31/1986     -1250.5    -0.13788
    6/30/1986        -944     -3.0608
>> SAData.date(1:5)
ans = 
  5×1 datetime array
   6/30/1985 
   9/30/1985 
   12/31/1985
   3/31/1986 
   6/30/1986 
>> 

so you can simply address whichever piece of the file you wish as the table column variable name and put them into whatever struct you wish. As the above shows, the date field is now a Matlab datetime array, the other two are doubles and there are no further cell arrays to do anything with nor to try to convert to datetimes--all done for you when you read the data in the correct format (and in this case, readtable is smart enough to figure it out for you automagically).

Rather than that, as you say, if the other function is a Matlab function, I'd probably just fix its interface to be more user friendly in taking fewer outside steps.

ADDENDUM

Note, however, you cannot put datetime array values into the same array as doubles -- the only way that can be is to use a cell array and that's an unnecessary complication it would seem.

But, if for some reason you are compelled to do so, you just dereference the cell array content with "the curlies", the {} braces instead of regular parentheses ()

[Ameer Fahmi]

Thanks for the nice explanation, as you said I shouldn't have pasted the data directly from excel to the mat file. Anyway I did load the data into Matlab using "readtable" function as you suggested.

The code of the function I'm using is an m-file, and you were right, I didn't need to follow the path written in the code to load the data, I used datenum function without the cell2mat function to seperate the date column

date=datenum(cell2mat(SAdata(2:end,1)),'mm/dd/yyyy');
date1=datenum(SAdata(2:end,1),'mm/dd/yyyy');

Then everything worked fine. I think using the cell2mat funaction here doesn't make any difference.

Thanks for your help, I really appreciate it.

[dpb]

date=datenum(cell2mat(SAdata(2:end,1)),'mm/dd/yyyy');
date1=datenum(SAdata(2:end,1),'mm/dd/yyyy');

Why in the world are you doing either of those operations!!!????

After reading SAdata.xlsx, as I showed, SAData.date is already a datetime array.

If this mysterious and apparently very badly constructed function must have datenum instead, then just convert the datetime to datenum directly--

date=datetime(SAData.date);

[Ameer Fahmi]

I had to do it, even though for me didn't make any sense, because the functions in the m-file code I'm using use the dates as numeric, and the code works fine with it.

[dpb]

Still, if you have to read the Excel file for the data, it's already parsed as datetime ignoring the header so just convert that representation to datenum directly instead of parsing the same data over yet again...

Or, of course, fix the function itself to use the more better datetime instead... :)

Answer 1

[Steven Lord]

If you want to store date and time data in MATLAB I strongly recommend storing it as a datetime array or in a timetable with a datetime array as the RowTimes property of the timetable. Once you have the data in a form that stores your data as datetime use something like writetable (introduced in release R2013a), writematrix (R2019a) or writetimetable (R2019a) or convert the datetime to an Excel serial date number with exceltime.

[Ameer Fahmi]

I tried what you've seggested, then I pasted the data I imported in form of timetable (SAdata1) to the GC mat file

GC.y=SAdata1;

But I had the same problem when trying to seperate the date column using the datenum function as follows

data=GC.y;
date=datenum(cell2mat(data(2:end,1)),'mm/dd/yyyy');

But this time I got different error

Error using cell2mat (line 42)
Subscripting a table using linear indexing (one subscript) or multidimensional indexing (three or more
subscripts) is not supported. Use a row subscript and a variable subscript.