How do I change a date in a cell into three cells in MatLab xlsx file?

I have an excel file that has a column of dates in an examples such as follows: 01-Oct-2022

How do I change it into three columns, so the day, month, and year can each be in their own cell? Like follows: 1 10 2022

I would like to do this in MatLab as opposed to Excel.

[the cyclist]

Can you upload the Excel file? You can use the paper clip icon in the INSERT section of the toolbar.

Excel is notoriously terrible at storing dates, so it is best to use your actual file, rather than guess at the multiple possible ways it could be stored.

[Macy]

Sure, just uploaded it, its a very large amount of data so I just included the first 10 rows to make it easier to work with. Thank you.

Answer 1

[Star Strider]

After using readtable to import the Excel file, try something like this —

T1 = table(datetime('now') + days(0:4).', rand(5,1), rand(5,1), 'VariableNames',{'DateTime','Data_1','Data_2'})

T1 = 5×3 table
          DateTime           Data_1       Data_2 
    ____________________    _________    ________

    05-Feb-2023 23:04:51      0.66889     0.43191
    06-Feb-2023 23:04:51      0.50427    0.094765
    07-Feb-2023 23:04:51      0.34119    0.089251
    08-Feb-2023 23:04:51    0.0026517     0.95882
    09-Feb-2023 23:04:51      0.82844     0.04634

[Year,Month,Day] = ymd(T1.DateTime);
T1 = addvars(T1,Day,Month,Year, 'After',1)

T1 = 5×6 table
          DateTime          Day    Month    Year     Data_1       Data_2 
    ____________________    ___    _____    ____    _________    ________

    05-Feb-2023 23:04:51     5       2      2023      0.66889     0.43191
    06-Feb-2023 23:04:51     6       2      2023      0.50427    0.094765
    07-Feb-2023 23:04:51     7       2      2023      0.34119    0.089251
    08-Feb-2023 23:04:51     8       2      2023    0.0026517     0.95882
    09-Feb-2023 23:04:51     9       2      2023      0.82844     0.04634

Make appropriate changes to your table to get the result you want.

EDIT — (5 Feb 2023 at 23:07)

With the provided file —

WaterData = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1285390/Water%20Data.xlsx', 'VariableNamingRule','preserve')

WaterData = 9×4 table
     Agency      site_no         Date        Q (cfs)
    ________    __________    ___________    _______

    {'USGS'}    1.1098e+07    01-Oct-1916      3.1  
    {'USGS'}    1.1098e+07    02-Oct-1916       40  
    {'USGS'}    1.1098e+07    03-Oct-1916       15  
    {'USGS'}    1.1098e+07    04-Oct-1916      6.5  
    {'USGS'}    1.1098e+07    05-Oct-1916      4.5  
    {'USGS'}    1.1098e+07    06-Oct-1916       10  
    {'USGS'}    1.1098e+07    07-Oct-1916        9  
    {'USGS'}    1.1098e+07    08-Oct-1916        6  
    {'USGS'}    1.1098e+07    09-Oct-1916        5  

[Year,Month,Day] = ymd(WaterData.Date);
WaterData = addvars(WaterData,Day,Month,Year, 'After',3)                % Option 1: Keep 'Date' (Recommended)

WaterData = 9×7 table
     Agency      site_no         Date        Day    Month    Year    Q (cfs)
    ________    __________    ___________    ___    _____    ____    _______

    {'USGS'}    1.1098e+07    01-Oct-1916     1      10      1916      3.1  
    {'USGS'}    1.1098e+07    02-Oct-1916     2      10      1916       40  
    {'USGS'}    1.1098e+07    03-Oct-1916     3      10      1916       15  
    {'USGS'}    1.1098e+07    04-Oct-1916     4      10      1916      6.5  
    {'USGS'}    1.1098e+07    05-Oct-1916     5      10      1916      4.5  
    {'USGS'}    1.1098e+07    06-Oct-1916     6      10      1916       10  
    {'USGS'}    1.1098e+07    07-Oct-1916     7      10      1916        9  
    {'USGS'}    1.1098e+07    08-Oct-1916     8      10      1916        6  
    {'USGS'}    1.1098e+07    09-Oct-1916     9      10      1916        5  

        
WaterDAta = removevars(WaterData, 'Date')                               % Option 2: Remove 'Date'

WaterDAta = 9×6 table
     Agency      site_no      Day    Month    Year    Q (cfs)
    ________    __________    ___    _____    ____    _______

    {'USGS'}    1.1098e+07     1      10      1916      3.1  
    {'USGS'}    1.1098e+07     2      10      1916       40  
    {'USGS'}    1.1098e+07     3      10      1916       15  
    {'USGS'}    1.1098e+07     4      10      1916      6.5  
    {'USGS'}    1.1098e+07     5      10      1916      4.5  
    {'USGS'}    1.1098e+07     6      10      1916       10  
    {'USGS'}    1.1098e+07     7      10      1916        9  
    {'USGS'}    1.1098e+07     8      10      1916        6  
    {'USGS'}    1.1098e+07     9      10      1916        5  

Use either ‘Option 1’,or ‘Option 2’ depending on the desired result.

.