Xcelsius and date formatting

[Former Member]

Hello everyone,

I have created a dashboard where I am importing data via a web service.  This data is to be displayed in a spreadsheet table.  One of the fields is a date.  It appears as a date when using SOAP, but once you bring it into an Excel chart, it converts it to a “General” text format (ex: 40359 should be 6/30/2010).   I’ve formatted those columns/fields in the Xcelsius Excel instance to be dates, but it still displays the dates as “40359” instead of “6/30/2010” etc.

Has anyone experienced this before?  Is it a glitch or is there an additional step needed for the data to be formatted properly?

Thank you all ahead of time for any insights you can provide.

Benson Yee

[Former Member · Accepted solution]

Thank you all so much for your replies!  I wound up formatting the fields, remapping and then attaching the data to a fresh and it worked!  I really appreciate your help.

Ben

[Former Member]

Hi Benson,

Your issue is common, and the standard way to deal with it is to convert the date from a number to a date string.  Say you have

A1 = 40359

Then you can define

B1 = TEXT(A1 , "m/dd/yyyy")

which will display as "6/30/2010" as desired.

For more information on the TEXT() function, see

http://office.microsoft.com/en-gb/excel-help/text-function-HP010062580.aspx

[former_member184839]

Hi Benson,

I've faced a similar issue before. I tried changing the cell formatting to date then remapped again. It worked for me.

Am not sure if it d work for you , but if it doesn't work , drag another spread sheet table component and map the things again.

But i would say, better use scorecard instead of the spread sheet table. because it provides you the formatting column wise. So If the remapping doesn't work in your case you can always format the columns in your scorecard component.

Even list view component would work well.

For the display purposes mostly the scorecard component is preferred, as its more advantageous than the spreadsheet table.

In the list view component you cannot make the "rows un-selectable" (there is no such option like that), whereas scorecard gives you that option .

Hope it Helps.

Thanks,

Sara

[Former Member]

Hi Benson,

This happens due to datatype set for dates on the universe level.

As a workaround, you may change the format of the cells to Date category and select date type.

Changing format of cells:

1. Select cell range in the excelsheet

2. Right click and select Format Cells

3. Select Date category

4. Select Type.

Map Spreadsheet Table component over the cells and preview the dashboard.

Thanks,

Tejas