Excel: Crosstab Cannot Group Using Custom Date Format

• Microsoft Excel for Windows, version 4.0
• Microsoft Excel for the Macintosh, version 4.0

SUMMARY

In Microsoft Excel version 4.0, if you have a column of dates formatted with a custom date format in a database, you will not be able to group your crosstab table by the dates in that column. Formatting the dates with one of the standard date formats in Microsoft Excel will enable the date groupings.

Custom date formats also cause the resulting crosstab to show serial numbers instead of a formatted date.

MORE INFORMATION

When grouping by dates, the Crosstab function compares the data to a list of built-in date formats. If a match is found, the "In Groups Of:" option in the Row or Column Category Options dialog box will have a drop-down list box containing various options for grouping the dates. If no match is found the "In Groups Of:" option will instead be a text box. Typing a group type in the text box you will result in the error message "Invalid value in GROUP field."

The Crosstab function will group by dates only when using one of the standard date formats.

REFERENCES

"User's Guide 1," version 4.0, page 354 "Function Reference," version 4.0, pages 82-84