Home : XLS files containing Cells with Dates (Data Type) appear as text when exported from Spread Reports
Q12761 - KNOWNISSUE: XLS files containing Cells with Dates (Data Type) appear as text when exported from Spread Reports

When exporting a spread report from WIMS (using File, Export, XLS (Microsoft Excel 97-2003) in Spread Design or Report Pac, Spread Reports and choosing XLS) dates are treated as text (i.e. General Cell Type) in Excel. 

If you change the format to Date it appears to have no effect.  Highlight B6:B12 and right click, go to Format Cells change format to Date:

 

Notice that the dates are still displayed as text.  Therefore, trying to sort by the dates does not work properly.

 

Workaround:

The following instructions work with Excel 2007.  Note: the Add-In is provided as is, use at your own risk.

Download and install SetAsDate.xlam Excel Add-In.

1. Click here to download, put file in your addins folder (C:\Documents and Settings\yourusername\Application Data\Microsoft\AddIns)

2. Get into Excel and go to Excel Options:

3. Go to Add-Ins Option page and click GO... 

4. Check Setasdate and click OK:

Use WIMS Format Date to set cell format

1. Highlight the exported dates (B6:B12)

2. Click on Add-Ins and pick WIMS Format Date.

The cells are now dates and can be formatted, sorted on, etc... 

Related Articles
No Related Articles Available.

Article Attachments
No Attachments Available.

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?

poor 
1
2
3
4
5
6
7
8
9
10

 excellent
Tell us why you rated the content this way. (optional)
 
Approved Comments...
No user comments available for this article.
Created on 12/23/2009 3:18 PM.
Last Modified on 12/28/2009 12:03 PM.
Last Modified by No Author Name Available!.
Article has been viewed 6520 times.
Rated 4 out of 10 based on 14 votes.
Print Article
Email Article