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

1 visualizzazione (ultimi 30 giorni)
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 Commenti
the cyclist
the cyclist il 5 Feb 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 il 5 Feb 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.

Accedi per commentare.

Risposta accettata

Star Strider
Star Strider il 5 Feb 2023
Modificato: Star Strider il 5 Feb 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.
.

Più risposte (0)

Prodotti


Release

R2022b

Community Treasure Hunt

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

Start Hunting!

Translated by