How determine date format in MATLAB

Question

I want to know in MATLAB which is the date pattern used by Excel. This is because I read an Excel file from MATLAB, but depending of the user machine locate the date is represented as dd-mm-yyyy or mm-dd-yyyy.

CLARIFICATION: Sorry for my bad explanation. This is my scenario. I have an Excel file with dates (and other collumns, no relevant for this problem). I have two computers, which need to run my matlab application. In the first one when I use xlsread (in MATLAB) the dates appears in dd-mm-yyyy format due to the regional configuration of my computer. In the second one, I read the same file, in the same MatLab version, but the readed dates are in mm-dd-yyyy format (again, due to the regional configuration of computer 2, which is different from computer 1).

Now, when I try to use datenum, to date transformation, I cant use formatIn parameter in a right way, because if I specify the formatIn equals to mm-dd-yyyy this will Works correctly in computer 1, by not in computer 2, and vice versa.

So, I think that I need to identify in MATLAB which is the date pattern used by Excel in the computer, in order to find the right input parameter for formatIn.

Answer 1

It is impossible to do unless you know your data really well. For instance if you have yearly readings for 01/07/20XX, it is impossible to know if it is 7th Jan or 1st July.

However, you can try the following:

MyString='01-23-2012';
FirstTwo=str2num(MyString(1:2));
if(FirstTwo>12)
    display('DD/MM');
else
    display('MM/DD');
end

If the first two digits of the date are greater than 12, then you can probably conclude that you have DD/MM/YYYY. You can loop this over all your dates.

Answer 2

If you're talking about an actual .xls file, I don't know enough to say if there's a some flag for this kind of thing, but one heuristic approach (and possibly the only approach with a CSV format) would be to look for numbers greater than 12. That will immediately tell you which format you have, because such a number can't be correspond to a month. Of course, with a small data set, this isn't reliable (strictly, it's never perfectly reliable, but with non-trivial data, it's highly likely to work).

Answer 3

You may be able to do something with Java to tell you the date format.

>> import java.text.DateFormat;
>> import java.text.SimpleDateFormat;
>> df = DateFormat.getDateInstance(DateFormat.SHORT);
>> dateFormat = char(df.toPattern())
dateFormat =
dd/MM/yy

I think xlsread uses this format, although you'll need to test it on both of your machines. Note there is also a Locale input to getDateInstance that may be useful.

Answer 4

I am kind of confused by your question, both MATLAB and Excel are able to easily support mm-dd and dd-mm. In excel, the default will depend on where you live. In America, it will be mm-dd, and in Europe (and probably most of the rest of the world), it will be dd-mm.

In MATLAB, I am not sure if it is location dependent like Excel is, as an American, the standard is of course mm-dd, but you can fully customize how matlab parses date strings!

Check out http://www.mathworks.com/help/matlab/ref/datenum.html and then go to input arguments, then "formatIn", it will provide you a list of ways to read in dates and convert it to a serial date number. (or vector if you want)

EDIT:

Nevermind, I misunderstood your question

Answer 5

I run into the same issue with computers from Australia and USA. This is a way around but it is a clean solution. In excel convert date to text for example in International format 'yyyymmdd'

 % B1=TEXT(A1,"yyyymmdd") % This is in excel
    % in matlab read excel file 'dates.xlsx'
    [data, dates_header] =xlsread('dates.xlsx');
    % use datevec to read-in data
    t = datevec(dates_header(:,2),'yyyymmdd');