how to convert dates in matlab

I have a series of dates such as

Date=[1/1/50
1/2/50
1/3/50
1/4/50
1/5/50
1/6/50
1/7/50
1/8/50
1/9/50
1/10/50
1/11/50
1/12/50]

being in the format of dd/mm/yy (from 1950 going to 2019)

how do i convert these numbers to dates because right now matlab calculates them as they are divisions.

[dpb]

Need to either

1. Read them in as text strings that can include the punctuation, or
2. Read them as numbers, but as separate vectors of year, month, day

Then use datetime to convert to the MATLAB class.

In what form do you have the data from which to start would be what we would need to know to give more precise code.

NB: You'll have some potential issues in use 2-digit years over the turn of the century; if you can clean up the input source to have four-digit years, you'll be well served. It can be made to work, but would be much cleaner the other way.

[Biki]

I've made it so the data is in dd/mm/yyyy and they are just copy pasted from excel onto matlab like shown in the question. What code would make it so that matlab recognizes this as a date? Thanks

Answer 1

[Star Strider]

I have no idea what file format they currently exist in, or how you are reading them.

Creating ‘datefile.txt’ from the posted vector in MS Notepad (attached), then reading them:

fidi = fopen('datefile.txt','rt');
D = textscan(fidi, '%s');
fclose(fidi)
DN = datenum([D{:}], 'dd/mm/yy')
DV = datevec(DN)
DT = datetime(D{:}, 'InputFormat','dd/MM/yy')

provides:

DV =

        2050           1           1           0           0           0
        2050           2           1           0           0           0
        2050           3           1           0           0           0
        2050           4           1           0           0           0
        2050           5           1           0           0           0
        2050           6           1           0           0           0
        2050           7           1           0           0           0
        2050           8           1           0           0           0
        2050           9           1           0           0           0
        2050          10           1           0           0           0
        2050          11           1           0           0           0
        2050          12           1           0           0           0


DT = 

  12×1 datetime array

   01-Jan-2050
   01-Feb-2050
   01-Mar-2050
   01-Apr-2050
   01-May-2050
   01-Jun-2050
   01-Jul-2050
   01-Aug-2050
   01-Sep-2050
   01-Oct-2050
   01-Nov-2050
   01-Dec-2050

so you will likely need to adjust them to the corect year.

[Biki]

I was able to adjust the data to make it dd/mm/yyyy and I just have the data,copy pasted matlab in square brackets like in the question. what code do i write?

[Star Strider]

That depends on the options you have available and your MATLAB version/release. The code I posted shoulld give you an idea of one approaoch.

[Biki]

Sorry I'm still a bit lost. I'm using R2020b (academic use) in macbook.

[Star Strider]

No worries!

I would use the readtable function to read the entire file (that I assume includes more than the dates).

I am still not certain how the dates were corrected, however if they were corrected in the original file, It should be straightforward to import them. It is also straightforward to extract the necessary information from the table. (I prefer readtable because it preserves the column header names, although if you do not need that information, use readmatrix instead.)

[Biki]

I have fixed the data in the original file in excel. I have tried to use readtable(making sure they the matlab file and the data is in the same folder) but i get an error saying

"Unable to find or open 'lakedata.xlsm'. Check the path and filename or file permissions."

I've tried to read data before on mac and it didnt work. So is there a way to convert the dates with what I've got in matlab now?

Thanks

[dpb]

"Unable to find or open 'lakedata.xlsm'. Check the path and filename or file permissions."

means the file is probably not in the current working directory or on matlabpath.

Use fullfile() with the directory and filename to create a fully-qualified filename.

fname=fullfile('theFileLocation',''lakedata.xlsm');
data=readtable(fname, ...)

where the ellipses in the last indicate any other needed input parameters like 'Sheet', 'Range', etc., ... that you'll have to supply.

There's no point in trying to cut and paste stuff; fix the issues to read the file instead.

Attach the file if you still can't get things to work.

[Biki]

I've got this error

"Unable to open file 'lakes.xlsx' as a workbook. Check

that the file exists, read access is available, and the

file is a valid spreadsheet file."

I've attached the data below

[dpb]

Show the code used to try to open it -- clearly the file isn't being found.

What does

dir lakes.xlsx

return? You said it was a .xlsm extension above.

[dpb]

>> lakes=readtable('lakes.xlsx');
>> head(lakes)
ans =
  8×20 table
       Date          D1        D2        D3        E1       E2       E3        E4       Fo1       Fo2       Fo3       Fo4       P1       P2        P3        P4       R1        R2        R3        R4  
    ___________    ______    ______    ______    ______    _____    _____    ______    ______    ______    ______    ______    _____    _____    ______    ______    _____    ______    ______    ______
    01-Jan-1950    141.98     70.95    199.31    106.15    85.17    41.41    102.69    1784.4    4374.7    5766.3      6025    85.96    89.35    149.48    105.39    26.84     93.05    343.54     234.8
    01-Feb-1950    124.74        72    206.96     53.26    54.91    40.38     87.73    1876.4    3805.5    5890.1    6253.9    28.39    58.17    101.76     79.69    21.82     60.27    174.06    119.92
    01-Mar-1950    109.45     74.32    196.16      31.2    37.28    29.24     53.82    1868.5    4035.7    5688.2    6624.9    47.68    61.62     81.02     75.23     30.2     98.69    238.74    275.72
    01-Apr-1950     97.82     84.21    164.47     22.12    21.46    15.51     26.48    1869.9    4561.9    6034.3    7368.4    66.02    77.43     89.82     48.93    58.76     137.8    200.89    384.66
    01-May-1950    252.64     74.52    162.69      1.43    -0.96     4.08     -0.68      1930    4764.4    6281.1    7548.7    92.49    40.71     39.12     42.14    217.8    101.86     48.25    108.95
    01-Jun-1950     67.64    110.55    163.94     -3.27    -2.65     40.7      14.1    2959.3    4980.6    6180.6    7460.3    99.39    80.91     66.29     64.19    81.45     56.36      43.6     79.67
    01-Jul-1950     67.76    114.19    162.26     -3.95     4.36    65.05     32.62    3490.8    5153.6    6013.4    7236.5    89.44    93.18     99.47     71.56     60.9     37.55     26.82     51.98
    01-Aug-1950     61.29    112.84    164.64      2.36    35.99    104.7     57.11    3576.9    5268.6    5823.6      7037    71.65     71.4     67.41     81.45    41.52     30.82     17.01     35.99
>> ans.Date
ans = 
  8×1 datetime array
   01-Jan-1950
   01-Feb-1950
   01-Mar-1950
   01-Apr-1950
   01-May-1950
   01-Jun-1950
   01-Jul-1950
   01-Aug-1950
>> 

is all you need and you get datetime for free.

Just have to determine where the file really is located and use the fully qualified filename to open it.

[Star Strider]

@dpb — Thank you!

[Biki]

thank you for both answers!