How do I change the data type of a field in Crystal Reports?

My data source is a spreadsheet extract. There are two fields with date values.  

The first, "Scheduled Date", contains only date values.  Crystal Reports recognizes this data type as DateTime (which is perfect).

The second, "Actual Date", contains date values and "TBD" values.  Changing these TBD values in the data source is not an option. Crystal Reports recognizes this data type as String (I want DateTime).

I would like to change the Actual Date field data type to DateTime so I can compare the two dates in a formula (if Actual Date > Scheduled Date .... etc.).

Please help

Mikeyro14Asked:
Who is Participating?
 
mlmccCommented:
Is there data in all rows for that column? or at least in the first 5-10 rows?
Crystal treats Excel a little funny.  If there is no data in the first few rows then the column is ignored.

mlmcc
0
 
mlmccCommented:
You can't change the data type since it isnt a date in all cases.

What you can do is to use a formula then compare against the formula

If ({ActualDateField} <> 'TBD') then
     Date({ActualDateField})
else
     Date(1901,01,01)

You can use whatever date you want as the default for TBD.  If you want the compare to be false then use a date that is later than any date in the database like Date(2050,12,31)

Another way would be to do the compare as

If ({ActualDateField} <> 'TBD') then
    Date({ActualDateField})  > {ScheduledDateField}
else
    FALSE  //Use TRUE if that is the result you want.

mlmcc
0
 
Mikeyro14Author Commented:
That solution is perfect for the problem I posted; however I just discovered another level of complexity.  I thank you for your response and hope you will be able to further assist me...

It seems the real issue is that Crystal Reports is not recognizing the date values in the Actual Date field.  If I pop the field into my report (either straight from the data source or as the formula you just shared), only the TBDs/ future dates show up.  None of the other dates show up at all.  

If I could get these dates to show up, then my problem would be solved.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
mlmccCommented:
Are you filtering the data in any way?

mlmcc
0
 
Mikeyro14Author Commented:
no
0
 
PCIIainCommented:
What is the datasource? Is it an ODBC connection, or a link to excel, or XML?
0
 
mlmccCommented:
Is this a single table?

Are you seeing all the records?

mlmcc
0
 
Mikeyro14Author Commented:
the data source is a single excel table/sheet.

I am seeing all the records, except for the dates previously mentioned.
0
 
Mikeyro14Author Commented:
there are no empty rows
0
 
Mikeyro14Author Commented:
However, if I sort the spreadsheet on the date field before connecting to Crystal Reports (thus bringing the dates into the first few rows), it recognizes the field as DateTime.

The TBDs are not recognized, but instead are null values, which I can work with.


SOLUTION!
0
 
Mikeyro14Author Commented:
I really appreciate the extra help as the nature of the problem shifted.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.