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

1 vue (au cours des 30 derniers jours)
Macy
Macy le 5 Fév 2023
Modifié(e) : Star Strider le 5 Fév 2023
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.
  2 commentaires
the cyclist
the cyclist le 5 Fév 2023
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
Macy le 5 Fév 2023
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.

Connectez-vous pour commenter.

Réponse acceptée

Star Strider
Star Strider le 5 Fév 2023
Modifié(e) : Star Strider le 5 Fév 2023
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.
.

Plus de réponses (0)

Produits


Version

R2022b

Community Treasure Hunt

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

Start Hunting!

Translated by