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

1 次查看（过去 30 天）
Macy
Macy 2023-2-5
编辑： Star Strider 2023-2-5
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 个评论
the cyclist
the cyclist 2023-2-5
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 2023-2-5
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.

请先登录，再进行评论。

请先登录，再回答此问题。

采纳的回答

Star Strider
Star Strider 2023-2-5
编辑：Star Strider 2023-2-5
Ran in:
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.
.

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

产品


版本

R2022b

Community Treasure Hunt

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

Start Hunting!

Translated by