Home » Categories » CHARTrunner

CR: Formatting an Excel Date

Revision Date: 2006-12-13

Problem:

In Microsoft Excel, you might have a date column where you have formatted the dates so they look like this:

Jan-03
Feb-03
Mar-03

However, when you use this date column as an identifier within CHARTrunner, and you ask for these to be displayed on the x-axis, your dates (on the chart) might look like this:

1-1-2003
2-1-2003
3-1-2003

CHARTrunner looks at the raw material in an Excel cell. It does not understand Excel date formatting. In this case, the raw material is a full date so that is what CHARTrunner uses.

Solution 1:

One way to solve this problem is to create a new column in your Excel sheet which contains a text version of the date information you want. For example, assume the dates start in column A, row 2

Put this formula in column B, row 2:

=text(a1, "mmm")

Next, put this formula in column C, row 2:

=text(a1, "yy")

And put this formula in column D, row2:

=B2&" "&C2

Finally, use column D as your identifier in CHARTrunner. Give it a name in Excel such as MonthAndYear. This puts a text version of the date in the Excel column. CHARTrunner will display the text as is.

Solution 2:

One way to solve this problem without changing your spreadsheet is to handle the date formatting in a custom query within CHARTrunner. On the data definition tab, rather than selecting the Table option, select Custom Query. Use the following query:

SELECT Format([Date], "mmm-yy") as MyFormattedDate, *from [Sheet1$]

This assumes the name of your sheet is "Sheet1" and that it contains a column named "Date". Next, when mapping the columns be sure to map the column named "MyFormattedDate" as an identifier. Also, be sure to select this as your x-axis label identifier.

In the query above, the asterisk indicates that you want to include ALL the fields from the sheet. Since we have also asked for a special field named MyFormattedDate, do not be surprised when you see two date columns in the resulting data. One will be your original date column and the other will be a formatted date column. We have used "mm-yy" in the query but there are several other formats that would work. Here are a few examples:

mmm-yy                       Feb-03
mm-yy                          02-03
mmmm-yy                    February-03

Attachments Attachments
There are no attachments for this article.
Related Articles RSS Feed
CR: Making a chart transportable
Viewed 2198 times since Fri, Nov 9, 2007
CR: Excel tips
Viewed 3125 times since Tue, Oct 6, 2009
CR: Background on out-of-control test rules
Viewed 2384 times since Fri, Nov 9, 2007
CR: The out-of-control (OOC) test may fail on the last sample with valid data
Viewed 2174 times since Fri, Nov 9, 2007
CR: CHARTrunner and SharePoint
Viewed 4214 times since Fri, Mar 9, 2012
CR: Label your X-Axis with more than one identifier
Viewed 2656 times since Tue, Oct 6, 2009
CR: Why is my first row of data not included when I get my chart from Excel
Viewed 2203 times since Fri, Nov 9, 2007
CR: CHARTrunner and Open Office
Viewed 2492 times since Fri, Nov 9, 2007
CR: Unable to fully display settings and windows
Viewed 2045 times since Fri, Jan 22, 2010
CR: CR1.1 - Charts in the Chart Workspace do not print all of the charts
Viewed 2128 times since Fri, Nov 9, 2007