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: Too many decimal places in the chart data grid for an Excel column that is calculated by a formula
Viewed 3689 times since Fri, Nov 9, 2007
CR: Collating sequence not supported
Viewed 2625 times since Tue, Dec 11, 2007
CR: Charting counted incidents
Viewed 1935 times since Thu, Feb 23, 2012
CR: CHARTrunner and Open Office
Viewed 2386 times since Fri, Nov 9, 2007
CR: Error “External table is not in the expected format” using Excel 2007 workbook
Viewed 4035 times since Wed, Oct 14, 2009
CR: Grouping counts by date in Excel
Viewed 2169 times since Fri, Nov 9, 2007
CR: Why is my first row of data not included when I get my chart from Excel
Viewed 2120 times since Fri, Nov 9, 2007
CR: The out-of-control (OOC) test may fail on the last sample with valid data
Viewed 2092 times since Fri, Nov 9, 2007
CR: Charts not working on a new PC
Viewed 2000 times since Fri, Nov 9, 2007
CR: Data grouping tardiness
Viewed 3160 times since Tue, Oct 6, 2009