Read the dates in the excel sheet

1 次查看（过去 30 天）
Divyashree
Divyashree 2024-9-30
编辑： dpb 2024-10-8
In the attached excel sheet, how to read the dates of the first and latest occurence 15 days before the CP (checkpoint) and 15 days after CP. The output sheet should be an excel sheet. It should again contain multiple sheets categorising the error status. Inside the excel sheet, sheet1 should contain only enabled states, sheet 2 should contain only disabled states and sheet three should contain locked states. Consider the date 10/15/2024 12:45, from checkpoint first it should read the dates 15 days before CP and 15 days after CP. First enable error states dates should be categorised. If enabled state is not there it should search for locked error state, if it is available it should categorise 15 days before and after CP. Also highlight the checkpoint.
How to proceed with this? as there is date inconsistency.
How to create a model and execute? When it is executed Excel sheet should be the output.

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

回答（1 个）

dpb
dpb 2024-9-30
编辑：dpb 2024-9-30
Ran in:
w=warning('off','MATLAB:table:ModifiedAndSavedVarnames'); % stop annoying warning about names
tS=readtable('sample.xlsx');
[head(tS);tail(tS)]
ans = 16x5 table
recordID task errorStatus firstOccurrence latestOccurrence ________ _________________ ____________ _______________ ________________ 1 {'write failure'} {'enabled' } 01-Sep-2024 01-Sep-2024 2 {'write failure'} {'enabled' } 16-Sep-2024 16-Sep-2024 3 {'write failure'} {'enabled' } 30-Sep-2024 30-Sep-2024 4 {'write failure'} {'locked' } 13-Apr-2024 13-Apr-2024 5 {'unlock error' } {'locked' } 26-Apr-2024 26-Apr-2024 6 {'fleet change' } {'enabled' } 15-Oct-2024 15-Oct-2024 7 {'unlock error' } {'locked' } 01-May-2024 01-May-2024 8 {'fleet change' } {'disabled'} NaT NaT 19 {'fleet change' } {'locked' } 15-Mar-2024 15-Mar-2024 20 {'write failure'} {'locked' } 01-Mar-2024 01-Mar-2024 21 {'fleet change' } {'enabled' } 28-Aug-2024 28-Aug-2024 22 {'leg swap' } {'locked' } 13-Feb-2024 13-Feb-2024 23 {'unlock error' } {'locked' } 29-Jan-2024 29-Jan-2024 24 {'fleet change' } {'enabled' } 13-Aug-2024 13-Aug-2024 25 {'leg swap' } {'enabled' } 29-Jul-2024 29-Jul-2024 26 {'leg swap' } {'enabled' } 15-Jul-2024 15-Jul-2024
tS.Properties.VariableNames=strrep(tS.Properties.VariableNames,'Occurrence','');
tS=convertvars(tS,'task','categorical');
categories(tS.task)
ans = 5x1 cell array
{'CP' } {'fleet change' } {'leg swap' } {'unlock error' } {'write failure'}
tS(tS.task=='CP',:)
ans = 1x5 table
recordID task errorStatus first latest ________ ____ ____________ _____ ______ 15 CP {'disabled'} NaT NaT
tS(tS.first>=datetime(2024,10,15)&tS.first<datetime(2024,10,16),:)
ans = 1x5 table
recordID task errorStatus first latest ________ ____________ ___________ ___________ ___________ 6 fleet change {'enabled'} 15-Oct-2024 15-Oct-2024
Your data file isn't suitable from which to work -- there is no CP record with any defined date and only one record for the 15th of October. But, the basic way to solve problem is illustrated above; datetimes can be compared like any other numeric field for equality or greater or less than...simply locate the starting points of interest and select records based on the time difference from that point forward/back.
datetime and <date-and-time-operations> doc links should get you started...
ADDENDUM:
To further illustrate the power and flexibility of the datetime class, another, simpler way to return the elements of a given day is--
doy=day(datetime(2024,10,15),'dayofyear') % the serial day of target in year
doy = 289
tS(day(tS.first,'dayofyear')==doy,:) % return all days which match that day
ans = 1x5 table
recordID task errorStatus first latest ________ ____________ ___________ ___________ ___________ 6 fleet change {'enabled'} 15-Oct-2024 15-Oct-2024
This is, of course, dependent upon leap years since there's the extra day in February so have to be a little careful...
  8 个评论
显示 6更早的评论
Divyashree
Divyashree 2024-10-7
One more thing, since the date formats are inconsistent, it can also be first organised which will make easier to read the 15 days dates without any errors.
dpb
dpb 2024-10-7
编辑：dpb 2024-10-8
""Again you've not answered the question of "which date"???" - The above mentioned is not specific to one date..."
That didn't answer the question. Is the reference to the first or last date column for the input and lookup...
" since the date formats are inconsistent, ..."
Inconsistent meaning what? Input format or order? If format, you'll have to fix that first; if order and if "organized" means sorted, then that's simple enough once you decide which date it is that is the controlling one above.
"The above logic I executed, but that is not what I expected. "
Well, I've asked several times for you to post a section of the file highlighted with what you did expect to try to get around the language problem and that your answers are so abbreviated without explaining what is meant precisely. Yes, you know what you mean; we can only try to understand by what you show/tell us which is incomplete and tends to simply repeat the same thing rather than illustrating...
Post the exact code you ran and then the results and then show us what you expected (and explain precisely why it is expected instead...). In it, however, what was returned were dates matching the values; what has not been answered is the question about whether it is only those dates or everything inclusive between the initial and the looked-for event date--

请先登录，再进行评论。

类别

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

标签

产品


版本

R2019b

Community Treasure Hunt

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

Start Hunting!

Translated by