identifying mistakes in date vectors

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"

Answer 1

[Walter Roberson]

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?

[antonet]

thanks Walter. Yes, duplicates and dates not on the first day of a month are considered to be mistakes. Put differently I must always have 01/mm/yyyy where mm must be increasing given yyyy.

[Walter Roberson]

Does any one file ever contain multiple years (even as a mistake) ?

[antonet]

Yes there were some files in which, for instance, the year 2005 appeared in the position of 2008. S0 2005 appeared 2 times which is a mistake

An example

 A={01/12/2005
01/01/2006
01/02/2006
01/03/2006
01/04/2006
01/05/2005
01/06/2006
01/07/2006}

[José-Luis]

Each "exception" has to be handled in the code. It can be a never ending story if you are dealing with human generated data. You would not believe the many possible ways to screw things up. One hundred files is not really that much data, if it looks like the snippet you provided. I would recommend writing a general import routine ( xlsread(), datenum(), etc...). Check for the most common errors you expect (ordered data, all the same year) and then manually check the rest. Things that are evident to the human eye may require a s**tload of coding.

[Walter Roberson]

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.