Problem reading excel dates with readtable on different computers

Hello,

I have made a script on my computer to read dates from an excel file, dates are in the format 'dd/mm/yyyy hh:mm' in the excel file, and it works perfectly. But then i tried the same script in another computer which has a different language settings and now readtable is reading the excel dates as 'mm/dd/yyyy hh:mm' which is a problem for the later parts of the script. To make things worst i tried this same script in a third computer which is in the same local language as the first one but readtable reads the dates as 'dd.mm.yyyy hh:mm' which is also a problem because i have a datenum command right after and it fails cause the format is different.

So my question is if theres a way to read the excel dates always on the format 'dd/mm/yyyy hh:mm' even if i run the script on different computers with different languages and different punctuations? So i dont have to change the datenum command every time i run the script on a new computer.

Thanks

Answer 1

[darova]

What about this smart approach?

mystr = '24/04/2020 11:12';
form = {'dd/mm/yyyy HH:MM'
        'mm/dd/yyyy HH:MM'
        'dd.mm.yyyy HH:MM'};
for i = 1:length(form)
    try
        datenum(mystr,form{i})
    catch exc
        sprintf('form %d is wrong',i)
%         rethrow(exc)
    end
end

[Sagasen]

Hey,

Thank you for your answer.

I found a solution few minutes after posting the question.

I used readtable(....,basic,true) so it reads the dates as excel serial numbers and then instead of doing datenum I do x2mdate.

[darova]

But how does it know the difference between these?

'dd/mm/yyyy HH:MM'
'mm/dd/yyyy HH:MM'

[Sagasen]

Im not entirely sure, but i think it reads the excel raw data without the visualization of 'dd/mm/yyyy HH:MM' because it says it will work even if i dont have excel installed

[darova]

Ok im not worried then