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

조회 수: 1 (최근 30일)
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일
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 CenterFile Exchange에서 Spreadsheets에 대해 자세히 알아보기

제품


릴리스

R2022b

Community Treasure Hunt

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

Start Hunting!

Translated by