Home » Categories » CHARTrunner

CR: Formatting an Excel Date

Revision Date: 2006-12-13


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


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:


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: Label your X-Axis with more than one identifier
Viewed 2565 times since Tue, Oct 6, 2009
CR: Histogram bars beyond specification lines
Viewed 2350 times since Fri, Nov 9, 2007
CR: Background on out-of-control test rules
Viewed 2325 times since Fri, Nov 9, 2007
CR: Charts not working on a new PC
Viewed 2049 times since Fri, Nov 9, 2007
CR: Some Excel Spreadsheet Values Seem To Be Missing
Viewed 2241 times since Fri, Nov 9, 2007
Cr: Charting elapsed time
Viewed 2483 times since Fri, Nov 9, 2007
CR: SQL Server Stored Procedures Returns No Records
Viewed 2298 times since Fri, Nov 9, 2007
CR: Data grouping tardiness
Viewed 3247 times since Tue, Oct 6, 2009
CR: Excel tips
Viewed 3067 times since Tue, Oct 6, 2009
CR: Show every date on the chart
Viewed 2243 times since Thu, Jan 31, 2008