SharePoint 2013 - Excel Web Part Chart Dates Issue

Question

I have created a bar chart in an Excel spread sheet and published it to a SharePoint 2013 document library. I want to show this graph directly on a SharePoint page and so added it as an Excel Web Access Web Part. The chart is displayed with the correct colours and other formatting, but the dates appear as 5 digit numbers, not in date format that was set in Excel "mmm yy".

Anybody know of a way to changes this format?

Answer 1

Hi Fraise,

We have received your Excel documents in private message. I used them for my test, and was able to display the date format correctly in the Excel Web Access web part. So it is not a client side issue or the document related.

 

To further look into the issue, please upload the documents to a new SharePoint site to check the outcome.

Meanwhile, please provide a screenshot of the Regional Settings of the problematic site. Click Gear icon > Site settings > Regional Settings.

Regards,

Jiaxing

Answer 2

Hi Fraise,

Can you share any update about the issue?

Regards,

Jiaxing

Answer 3

Hi Fraise,

Thanks for your detailed information.

For further investigating, please also share with us the affected Excel file and a video showing how you reproduced the issue. I have sent you a private message to collect the files, which can be accessed via the link below:

https://answers.microsoft.com/en-us/privatemessages/list

Regards,

Jiaxing

Answer 4

Hi Jiaxing,

Thanks for replying, here's the information you asked for:

1. In the source table shows the dates correctly but see answer 4 and the table below.
2. The date format is displayed correctly in the bar chart in Excel. I've tried something new though. If I leave it autoformatted as a date dd/mm/yyyy it displays correctly in SharePoint, but in Excel I change the format to mmm yy which changes it from a date format into a custom format and that doesn't display in SharePoint.
3. See above, dates do display correctly, but custom does not.
4. To create the timeline barchart I am adding dates together. The first column is the same date 1/1/15, then each of the following columns in the table add to this. So for example if inception starts at 15/1/15, then I add 14 to column 1 to get column 2 and then for column 3 add to that to get the next date. All the columns are formatted as dates though, so column 2 in this example is 14/01/1900 (see row 2 of the below table). It looks like this:

	Start	Inception	Development	QA	Release	Closure
01/01/2015	00/01/1900	29/09/1900	07/03/1900	00/01/1900	24/01/1902	05/01/1900
01/01/2015	14/01/1900	00/01/1900	29/12/1901	00/01/1900	03/03/1900	05/01/1900
01/01/2015	23/05/1901	00/01/1900	04/09/1900	07/02/1900	08/01/1900	05/01/1900
01/01/2015	24/03/1901	21/02/1900	12/09/1900	07/02/1900	08/01/1900	05/01/1900
01/01/2015	23/05/1901	01/06/1900	18/04/1900	28/01/1900	04/01/1900	05/01/1900
01/01/2015	05/05/1901	17/01/1900	19/09/1900	31/01/1900	03/01/1900	05/01/1900
01/01/2015	00/01/1900	00/01/1900	15/03/1902	05/01/1900	03/01/1900	05/01/1900
01/01/2015	10/04/1901	24/03/1900	09/09/1900	17/01/1900	31/03/1900	05/01/1900
01/01/2015	05/04/1901	00/01/1900	24/12/1900	00/01/1900	00/01/1900
01/01/2015	29/05/1901	00/01/1900	26/09/1900	31/03/1900	03/01/1900	05/01/1900

I believe because of this, when I format the axis, there is no 'Axis Type' section that would allow me to select Date Axis, and therefore the bounds are in number format and not date format. This means that I can't increment the units by 1 month and have to choose 31 day increment, which means that my dates move up from the 1st of the month for any dates following a 30 or 28 day month. This is why I formatted it mmm yy without the day.

Hope this provides enough information. So I guess my original query is now, how can you get SharePoint to display customer axis formatting?

Thanks again,

Fraise

Answer 5

Hi Fraise311,

To look into the issue, please confirm the following information:

1. Is the date format displayed correctly in the source table?
2. Is the date format displayed correctly in bar chart when opening from Excel client?
3. Create a new bar chart to check the outcome
4. Provide your detailed steps to create the problematic Excel spreadsheet.

Regards,

Jiaxing