I have written before about data type changes in Excel. One article explained how to convert a column of numbers into character values so that they can link correctly to another column of character values.

Today I was asked how to do something similar with dates. Two spreadsheets were being linked on a date field, and Crystal was reading one field as a true date and one as a string date. I found a simple way to get Crystal to recognize the string as a date. I created another column that was simply the Date String column plus zero [e.g. =A3 + 0].  This turned the new column into a number representing the date. Then I formatted that column as dates and that allowed Crystal to recognize the new column as a date.

It surprised me to find that I had to format the field as a date. When it comes to dealing with strings and numbers the formatting is usually irrelevant, but apparently with dates the format is important. When I tried to skip formatting the column as a date, Crystal read the new column as numeric values.