Discover MakerZone

MATLAB and Simulink resources for Arduino, LEGO, and Raspberry Pi

Learn more

Discover what MATLAB® can do for your career.

Opportunities for recent engineering grads.

Apply Today

Thread Subject:
Reliable date transfer to Excel

Subject: Reliable date transfer to Excel

From: Bradley Steel

Date: 27 Jan, 2014 12:48:06

Message: 1 of 3

I have a project where we write dates to a pre-formatted excel sheet, using xlswrite. Dates are stored as datenumbers and to get them into excel I converted them to a string using datestr(val); the target cells are preformatted to date format. This writes dates in the form '15-Oct-2011' and was parsed into dates by Excel.

This works fine on our systems, but we recently sold the project to a German client who had a large number of errors. The problem turned out to be that the German version of Excel does not recognise '15-Oct-2011' as a date, expecting it to be '10-Okt-2011', and hence Excel considered it a text string not a date. Likewise, 15/10/2011 failed, and on-site we chose to use '15.10.2011' as an output format as this was recognised as a date.

Unfortunately, having returned home I find that '15.10.2011' is not parsed by our local (UK) system as an Excel date, and so we can't incorporate this fix into our main code line as it won't work for UK systems.

The issue stems from having to export a string and rely on Excel to recognise that string as a date. Is there any better way (ie, a way to directly export a 'date', since Excel stores dates numerically anyway)?

As a poor man's alternative, does anyone know of a string format that will be universally recognised as the correct date in UK (27/1/2011) , US (1/27/2013) and Germany (27.01.2013) ?

Subject: Reliable date transfer to Excel

From: Kevin Ellis

Date: 27 Jan, 2014 16:45:15

Message: 2 of 3

"Bradley Steel" <starstrike99@yahoo.com.au> wrote in message <lc5ki6$9cn$1@newscl01ah.mathworks.com>...
> I have a project where we write dates to a pre-formatted excel sheet, using xlswrite. Dates are stored as datenumbers and to get them into excel I converted them to a string using datestr(val); the target cells are preformatted to date format. This writes dates in the form '15-Oct-2011' and was parsed into dates by Excel.
>
> This works fine on our systems, but we recently sold the project to a German client who had a large number of errors. The problem turned out to be that the German version of Excel does not recognise '15-Oct-2011' as a date, expecting it to be '10-Okt-2011', and hence Excel considered it a text string not a date. Likewise, 15/10/2011 failed, and on-site we chose to use '15.10.2011' as an output format as this was recognised as a date.
>
> Unfortunately, having returned home I find that '15.10.2011' is not parsed by our local (UK) system as an Excel date, and so we can't incorporate this fix into our main code line as it won't work for UK systems.
>
> The issue stems from having to export a string and rely on Excel to recognise that string as a date. Is there any better way (ie, a way to directly export a 'date', since Excel stores dates numerically anyway)?
>
> As a poor man's alternative, does anyone know of a string format that will be universally recognised as the correct date in UK (27/1/2011) , US (1/27/2013) and Germany (27.01.2013) ?

Bradley,

I believe I found an answer to your question. In order to change the format in Excel from MatLab you have to use 'actxserver.' Since I don't know the details of your project and depending on how difficult it is, I would use 'actxserver' and 'export' to export data to Excel. I always had difficulty using 'xlswrite.' Anyways, I created some code that allows can be placed after the point where you export your data to Excel. All it does is open a link to the Excel file and formats the column for the range of dates. Here is the code:

%Initialize ActiveX Controller & Turn Off Displays/Sounds
    ExcelObject = actxserver('Excel.Application');
    ExcelObject.EnableSound = false;
    ExcelObject.DisplayAlerts = false;
%Initialize File Pattern
    MyFolder = ['C:\Users\Kevin_Ellis\Desktop\Test'];
    FilePattern = fullfile(MyFolder,'*.xlsx'); %EXCEL FILE TYPE!!!
    Files = dir(FilePattern);
%Create FileName
    FileName = Files.name;
    FullFileName = fullfile(MyFolder,FileName);
%Open Workbook
    ExcelWorkbook = ExcelObject.Workbooks.Open(FullFileName);
    ExcelWorksheet = ExcelWorkbook.ActiveSheet;
%Format Dates
    %ExcelWorksheet.Range('A1').EntireColumn.NumberFormat = 'mm/dd/yyyy'; %US
    %ExcelWorksheet.Range('A1').EntireColumn.NumberFormat = 'dd/mm/yyyy'; %UK
    ExcelWorksheet.Range('A1').EntireColumn.NumberFormat = 'dd.mm.yy'; %German
%Save & Close
    ExcelWorkbook.Save;
    ExcelWorkbook.Close;

The first and second part creates the connection using 'actxserver.' You have to specify the folder which contains the file you are exporting the data to. For me, I created a test case with a single Excel file with a column of dates in it on my desktop. The third part finds all the files in that folder and creates a 'FullFileName' which for my test case is 'C:\Users\Kevin_Ellis\Desktop\Test\Test.xlsx.' If you have many excel files in your folder I would delete the second and third part of the code and directly link it to the Excel file by saying something like:

FullFileName = 'C:\Users\Kevin_Ellis\Desktop\Test\Test.xlsx'

The fourth part of the code opens the workbook and opens the active worksheet (where you want to format your dates). The fifth part formats the dates using the .NumberFormat property of EntireColumn. There are three lines of code: the first formats to US dates, the second to UK dates, and the third to German dates. Depending on who you are giving the code to you can swap or comment out these lines to get specified dates. The last part of the code saves the changes and closes the workbook.

In my job I work with databases/excel files and use 'actxserver' alot. Microsoft has created the following documentation which works with 'actxserver'

http://msdn.microsoft.com/en-us/library/bb213677

and allows you change pretty much anything from MatLab (color of cells, font, bold, etc.). Anyways, hope this helps.

Kevin

Subject: Reliable date transfer to Excel

From: TideMan

Date: 27 Jan, 2014 19:02:32

Message: 3 of 3

On Tuesday, January 28, 2014 1:48:06 AM UTC+13, Bradley Steel wrote:
> I have a project where we write dates to a pre-formatted excel sheet, using xlswrite. Dates are stored as datenumbers and to get them into excel I converted them to a string using datestr(val); the target cells are preformatted to date format. This writes dates in the form '15-Oct-2011' and was parsed into dates by Excel.
>
>
>
> This works fine on our systems, but we recently sold the project to a German client who had a large number of errors. The problem turned out to be that the German version of Excel does not recognise '15-Oct-2011' as a date, expecting it to be '10-Okt-2011', and hence Excel considered it a text string not a date. Likewise, 15/10/2011 failed, and on-site we chose to use '15.10.2011' as an output format as this was recognised as a date.
>
>
>
> Unfortunately, having returned home I find that '15.10.2011' is not parsed by our local (UK) system as an Excel date, and so we can't incorporate this fix into our main code line as it won't work for UK systems.
>
>
>
> The issue stems from having to export a string and rely on Excel to recognise that string as a date. Is there any better way (ie, a way to directly export a 'date', since Excel stores dates numerically anyway)?
>
>
>
> As a poor man's alternative, does anyone know of a string format that will be universally recognised as the correct date in UK (27/1/2011) , US (1/27/2013) and Germany (27.01.2013) ?

Export it to Excel as a datenumber, then convert to the appropriate format within Excel.
BUT you must subtract 693960 days from the datenum to get Excel days:
t_excel=t_matlab - 693960;
Also, if your dates are before 01-Mar-1900, you must realise that the ignoramuses at Excel did not realise that 29-Feb only occurs in centuries that are divisable by 4, i.e., in 1600, 2000, 2400, etc.
 

Tags for this Thread

What are tags?

A tag is like a keyword or category label associated with each thread. Tags make it easier for you to find threads of interest.

Anyone can tag a thread. Tags are public and visible to everyone.

Contact us