Read the dates in the excel sheet

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.

Answer 1

[dpb]

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...

[Divyashree]

Hello, The above solution did not work for me. Consider the removal date 6/30/2024 which is mentioned in another excel sheet. The matlab reads this date from the excel and consider this as the main date. Keeping this date as point of interest, the matlab should read the dates in the sample excel sheet which I've attached again. it should read only the valid dates before CP(checkpoint) 15 days and 15 days after CP. First it should look for enabled error status, if it not there it should look for locked status.

NOTE: Consider 0000-NA-00 00:00:00 and 0000-01-01 00:00:00 as default dates because they are invalid.

Suppose if 15 days date if not there, it can go back one day before. If one day before date is not found, it can go back to 1 week before max.

(I've again attached the modified excel sheet)

[dpb]

"NOTE: Consider 0000-NA-00 00:00:00 and 0000-01-01 00:00:00 as default dates because they are invalid."

What does that mean? What is a "default" date and what is one supposed to do with them?

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-Mar-2024        01-Sep-2024   
        2       {'write failure'}    {'enabled' }      16-Mar-2024        16-Sep-2024   
        3       {'write failure'}    {'enabled' }      30-Mar-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-May-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-Feb-2024        15-Mar-2024   
       20       {'write failure'}    {'locked'  }      01-Feb-2024        01-Mar-2024   
       21       {'fleet change' }    {'enabled' }      28-Feb-2024        28-Aug-2024   
       22       {'leg swap'     }    {'locked'  }      13-Feb-2024        13-Feb-2024   
       23       {'unlock error' }    {'locked'  }      29-Feb-2024        29-Jan-2024   
       24       {'fleet change' }    {'enabled' }      13-Mar-2024        13-Aug-2024   
       25       {'leg swap'     }    {'enabled' }      29-Mar-2024        29-Jul-2024   
       26       {'leg swap'     }    {'enabled' }      15-Mar-2024        15-Jul-2024   

tS.Properties.VariableNames=strrep(tS.Properties.VariableNames,'Occurrence','');
tS=convertvars(tS,@iscellstr,'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  

refDate=datetime(2024,6,30);
deltaT=days(15); % a reference time before/after for envelope
tS(tS.first>=refDate-deltaT & tS.latest<=refDate+deltaT,:)

ans = 2x5 table
    recordID        task        errorStatus       first         latest   
    ________    ____________    ___________    ___________    ___________

       12       leg swap          locked       16-Jun-2024    16-Jun-2024
       14       fleet change      locked       30-Jun-2024    30-Jun-2024

tS.first.Format='default'; tS.latest.Format='default';
tWithinDelta=tS(tS.first>=refDate-deltaT & tS.latest<=refDate+deltaT,:);
if ~any(tWithinDelta.errorStatus=="enabled")
    deltaT=deltaT+7; % add another week max
    tWithinDelta=tS(tS.first>=refDate-deltaT & tS.latest<=refDate+deltaT,:)
end

tWithinDelta = 2x5 table
    recordID        task        errorStatus           first                   latest       
    ________    ____________    ___________    ____________________    ____________________

       12       leg swap          locked       16-Jun-2024 16:00:00    16-Jun-2024 16:00:00
       14       fleet change      locked       30-Jun-2024 15:45:00    30-Jun-2024 15:45:00

And there are still none showing either 'enabled' or 'CP' in the area around the requested date; one presumes in a full file or for other dates one would eventually find such.

The real logic behind what it is that is being looked for is still quite unclear -- is it to find something around an arbitrary date, or find a given event and then see when it occurred?

Either way, the above illustrates how to approach it; first decide what is the primary thing you're looking for to isolate around, a time or an event (the above assumes time). Then set the time (again whether is first or last is also not defined here) and select the window around that point. You can then simply test for the existence of the target within that range of responses.

Truthfully, it seems one might as well just set the maximum range before/after the target time, however it is determined (from a specific date or the occurence of a given event time) and extract that range of data; it can then be pared down to the first and last events of interest withn that result eliminating those before and after simpler than searching a record at a time.

Or, of course, you could find/set that target time and then do a find() on the dual condition of the event and a time span.

It would probably help to provide a section of the file that is representative of what you're looking for as text and then explain within it specifically what you are looking for as a result; it doesn't appear that the data around the places you've pointed to does match up with your expectation of what you would want...

[dpb]

ADDENDUM

It also doesn't appear that input file is actually in chronological order; there are first dates that are earlier than others -- has the file been sorted by some other means or what? In the present order, if one were to return the indices of a given start, end time, then the entries contained within that time frame appear as though they could be almost any date; not just within those two dates. What does that mean? Is it important?

The above code filters the returned records based on the timestamps actually satisfying the condition and will exclude any records outside those time bounds. Is that what is intended?

Some many questions, so few answers...

[Divyashree]

Invalid dates are kept default because those dates do not change and they are same for other units as well.

Only valid dates are considered and read.

Invalid dates are not considered which is why I mentioned that invalid dates should be default.

There are two excel sheets, one is service maintainance data and the other is NVM fault history file.

In the service maintainance file there is removal date mentioned. Considering that date it should read the 15 days date in the NVM file.

I've updated the excel sheet, it should read first the active state 15 days before NAV and then the latched states 15 days before NAV.

Suppose if the 15 days date is not found, then it should search for the date which is nearer to the 15 days for both active and latched state.

After NAV is not required.

[dpb]

Again you've not answered the question of "which date"???

But, I've shown you all the logic needed to extract any section you wish relative to whatever time you wish...

[Divyashree]

The above logic I executed, but that is not what I expected.

"Again you've not answered the question of "which date"???" - The above mentioned is not specific to one date, it should read multiple dates just before the NAV.

In the recently updated excel sheet, in Service maintainance data there is removal date mentioned. Considering that date, read all the valid dates in the NVM fault history file before the NAV only for active and latched states. Invalid dates are not required (0000-NA-00 00:00:00, 0000-01-01 00:00:00). Output should be an excel sheet, which consists of only valid dates for active and latched states. In one excel ,multiple sheets should be there. One sheet is for active state and the other sheet is for latched states.

Valid dates should be 15 days before NAV marker. If there is no exact 15 days difference between the dates, then the nearest date to the 15 days can be read.

[Divyashree]

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]

""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--