identifying mistakes in date vectors

1 view (last 30 days)
Dear all,
I have many excel files and in each excel I have a column under the name "dates". The date format is dd/mm/yyy and the data are monthly
For instance,
01/12/2005
01/01/2006
01/02/2006
01/03/2006 and so on
Since I have a large number of excel files (around 100) I want to see in each file if the sequence of dates is disrupted by mistakes
For instance, the below date sequence contains mistakes
A={01/12/2005
01/01/2006
01/02/2006
01/03/2006
01/04/2006
01/07/2006
01/06/2001
12/07/2006}
Is there any way to identify these mistakes?
Thanks
PS:Note that the start date and end date are different across files. So if you provide any code, please take into account that I need to be able to find the initial date "automatically"

Accepted Answer

Walter Roberson
Walter Roberson on 19 Feb 2013
You did not define "mistake". It appears that "out of sequence" is a mistake; how about duplicates? Are missing values mistakes? Are dates not on the first day of a month mistakes?
  5 Comments
Walter Roberson
Walter Roberson on 19 Feb 2013
Okay then what I would suggest is using datevec() on the input dates.
If the day of the month of the datevec are not all 1 then you have day of month problems.
diff() the datevec first column. If the year differences are not all 0's or 1's (in particular if there are negatives) then you have year problems.
diff() the datevec second column (month). If the month differences are not all 1's or -11's, or if the -11 do not correspond to the month number becoming 1, then you have month problems.
Ummm, I think that might be good enough, once you add a little more logic to select / display the entry in error. As you asked only to "find out" whether there were errors, rather than to (e.g.) sort the dates as well, it sounds to me as if your plan is to manually look at and repair the problem entries in excel before running again to recheck.

Sign in to comment.

More Answers (0)

Categories

Find more on Data Type Identification in Help Center and File Exchange

Tags

Community Treasure Hunt

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

Start Hunting!

Translated by