PQ Systems Knowledge Base :: CR: Formatting an Excel Date

Search the Knowledgebase

Browse by Category

Knowledgebase Home > 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