conversion of date/time error when using easy excel reports

[David_NMRSA]

I am exporting a 2D array to an excel file using Easy Excel Reports vi. I have no problem with this. My data has a number of fields with different formats (date/time and text). My problem is as follows:

* some (that is, not all data does this in same spreadsheet) of the date/time values covert to the mm/dd/yyyy format when they should be in the dd/mm/yyyy (this is the format excel says it is in!). Essentially, I will have 30/09/2008 (30 Sept 2008) in a cell. As soon as the next day ticks over to Oct 1 it will display the date as, 10/01/2008. Excel thinks this date is in the dd/mm/yyyy format.

This is a known error in previous versions of Excel (2002), especially when converting a .csv file to .xls. The solution there was to add "Local:=True" at the end of the vba command string (see here: MS Knowledge Base). Here, in LabView i'm not so sure how to do this.

I have tried to change the format of the column in question to "Text" by calling an Excel Macro in LabView but do you think I can find the vba command to do so ? No, not so.

LabView saves the Excel file in a 97-2003 format (i'm using MSOffice 2007) and I wonder if the version they use does not consider the date/time fix as described in the knowledge base (or if it even applies).

Any suggestions, I would love to hear from you.

Regards,

David.

[LaRisa_M]

Hi David,

Something that might work for you is using the Excel Set Format VI. If you search the LabVIEW help for "Excel Set Format" (the quotes help), there's an example at the bottom of the article. You should be able to use this VI to set the type of date formatting you want for those cells. You may need to do some research into what the exact String command for European dates is, but with a little trial and error I think this will work for you.

If you have any other quesitons about it, posting your VI or letting us know the version of LabVIEW you're using would be helpful.

[David_NMRSA]

Thank-you LaRisa. I will have a look at this VI and let you know how I panned out.

I'm all spec'd up with LabView 8.6 and Windows XP.

Posting my code would be of no benefit. The problem can be easily reproduced by sending a string to an Easy Excel Table vi (first creating a 2D array with the string). This string would be a date in the format  "dd/mm/yyyy". I am using the Australian date format in Excel. If using other (say USA) then change the format string to "mm/dd/yyyy". Viewing the result in the excel sheet will show you the problem I am having. That is, it is not necessarily LabView but the conversion of data between LabView and MS Excel (so I believe).

Regards,

David.

[David_NMRSA · Accepted solution]

Hi LaRisa,

I was able to find the Excel Set Cell Format vi and successfully alter the format of the cell value. It wasn't until I had found the vi that I realised that i've been down this path before. Changing the format after the table has been created will not alter the switching of dd and mm values that has already occurred. However, if I did it before I 'created the table' (or Excel Table vi) and set the format to be "@" then it will display the string as 'Text' in the Excel file. This is nice. I can control which cell, row or column to format now. If I want it to be in a date format then I have to apply the Cell Format vi again after the Easy Excel Table vi.

This seems like a silly run around to me....

[user_1]

Hi... Am also having the same problem while exporting to excel...

I used the Excel Set column Format.vi...but i dont know how to use that properly.

Bse even i used that vi,it cudnt make any difference.So cn u pls guide me.

In the 2D array giving to exceleasytable.vi,the first column of the 2D array is always date.

I want to set the date as dd/mm/yyyy.

So kindly guide me 

[LaRisa_M]

Hi,

Take a look at the Help document for that VI and let us know if there's anything specific you're having trouble with: http://zone.ni.com/reference/en-XX/help/372120A-01/lvoffice/excel_set_cell_format/#examples

As David mentioned, be sure to use the Excel Set Cell Format before the Easy Table VI. The Format VI won't change a table that was already created.

[user_1]

Hi...

Thanks a lot for ur reply.

I tried wth format cell.vi in excel already..But that also not helping me.

Am attaching the codesnap..

Pls guide me to know where am i going wrong..If u have any sample vis for my issue,pls send that too

Thanks again....