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: Show every date on the chart
Viewed 2190 times since Thu, Jan 31, 2008
CR: Largest Subgroup Size Supported by CHARTrunner
Viewed 1983 times since Fri, Nov 9, 2007
CR: Charting pass-fail data as a p-chart
Viewed 4354 times since Fri, Feb 29, 2008
CR: Making a chart transportable
Viewed 2072 times since Fri, Nov 9, 2007
CR: Charting counted incidents
Viewed 1871 times since Thu, Feb 23, 2012
CR: Multiple chart lines from one data column
Viewed 2181 times since Fri, Nov 9, 2007
CR: Why is my first row of data not included when I get my chart from Excel
Viewed 2076 times since Fri, Nov 9, 2007
CR: Label your X-Axis with more than one identifier
Viewed 2471 times since Tue, Oct 6, 2009
CR: Histogram bars beyond specification lines
Viewed 2268 times since Fri, Nov 9, 2007
CR: General Filtering Tips
Viewed 2164 times since Fri, Nov 9, 2007