Time Date field in Excel seen as string in Crystal Reports

[Former Member]

I am trying to create a crosstab, grouping columns by day.  However, this isn't working because Crystal sees my time date field in Excel as a string.

Is there is a workaround?

Thanks.

[former_member292966 · Accepted solution]

Hi,

For Crystal to determine the datatype it reads the first row and makes a guess.  If your datetime column is empty in the first row, it assumes it's a string.

Like Antonio said, you can get around it using a formula but you should check the value before doing the conversion.  Instead of using cdate use cdatetime like:

If isDateTime ({Times_.TimeIn}) then

     CDateTime ({Times_.TimeIn})

else CDateTime (0, 0, 0, 0, 0, 0);

This checks the field for a datetime and if it is then it does a conversion.  If it doesn't then it passes a null datetime.

Crystal will have an issue with the :990 portion of the time.  It only works to the second.

Hope this helps,

Brian

[Former Member · Accepted solution]

Okay.  Tried your formula and did receive all null values.

So, it looks like it just will not work?

[former_member292966 · Accepted solution]

Hi,

You will need to strip off the decimal part of the time before you can do the convert.  Crystal only recognizes up to the seconds in the time portion.  Once you do that then it should work.

Brian

[Former Member · Accepted solution]

Can you help me do that?

The Excel spreadsheet is coming from a third party source.  When I highlight the column of datetimes and try to change the format of the cells, it does nothing.  I even copied the column to a new column and tried it that way.

it allows me to manually type in new data in the format I want and save the changes.  However, there are hundreds of rows of data, so this would be impractical for me.

Or is is there a formula that can be written in Crystal to truncate the date time that way?

i just need the date part.

Thanks.

[Former Member · Accepted solution]

Hi,

Then you would need to play with some string functions.

Below is a function I created to extract the date from your field.

cdate(left({Your string here},instrrev({Your string here},":")-1))

Building from what Brian mentioned, this formula removes the milliseconds from your string. After that it should correctly cast into a date.

[Former Member · Accepted solution]

I am getting an error.  See attached photo.

I used your formula to create a formula called "StringToDate" which I inserted into my crosstab instead of the original timedate field.

Am I doing something wrong?

[former_member292966 · Accepted solution]

Hi,

Antonio's formula almost works but in your case two things need to change: you need to see if the value is empty or not and you need to convert to DateTime, not just tdate.

Try this:

if length ({Times_.TimeIn}) > 10 then

    cDateTime (left (mydt,instrrev ({Times_.TimeIn},":")-1))

Else DateTime (0, 0, 0, 0, 0, 0);

I used the length to see if the field is empty or not.  You can check for other things like a ":" or something else.

If there is a value then it converts the field to a date time. Otherwise it passes a null date time.

Brian

[Former Member · Accepted solution]

Brian,

When trying your revision of the formula, I get an error at the (mydt   part saying we are missing a )

Sorry, I don't know where this should be inserted.

[former_member292966 · Accepted solution]

Sorry.  My editing missed something.  Try this:

if length ({Times_.TimeIn}) > 10 then

    cDateTime (left ({Times_.TimeIn},instrrev ({Times_.TimeIn},":")-1))

Else DateTime (0, 0, 0, 0, 0, 0);

In place of your field, I used a variable called mydt.  I missed it in one place.

Brian

[Former Member · Accepted solution]

Brian,

Your formula is working!   I have dates now.

Thanks to all for your help!

[Former Member · Accepted solution]

Sorry, one more question.  Now that I have some cells showing 00.00.00 in my Crosstab, how can I suppress those to only show a blank instead of the zeros?

I would guess I would use a suppression formula when formatting the field, but I am not sure how to write the formula.

Thanks.

[Former Member · Accepted solution]

Actually, instead of using length you should be using the isDate function.

if isdate(left({@String},instrrev({@String},":")-1)) then

    cdate(left({@String},instrrev({@String},":")-1))

else

    cdate('1990-1-1')

CDate and CDateTime for your purposes wouldn't matter at all actually but with the isDate function, if the field is not able to be changed into a date, it would provide a default date. This is more reliable than testing the length of the string.

[Former Member · Accepted solution]

Hi,

Before suppressing the 00.00.00 in your Crosstab, I would figure out why you are getting the 00.00.00 values. The code Brian provided to you gave you a default value in the case that the string length was under 10 characters length. Please provide the original date values from the excel, where you are getting the 00.00.00 values.

[Former Member · Accepted solution]

I believe I know why I am getting the 00.00.00.  This is not generating in the cells Brian helped me with.  A new column with zeros is being created in my crosstab because of the design of the Excel spreadsheet.  There is a calculated "total" that they inserted in a separate row beneath all the "time in" dates we are using in the crosstab.

This "total", unfortunately, does not have the "time in" date associated with it in the same row, so the zeros are showing up.

I still want this "total" to show up in my crosstab.  I just want to suppress all the zeros that are now being created and prefer to have blanks.

[Former Member · Accepted solution]

You can just filter out the total row by using a record selection formula. For example, you can enter in a record selection formula such as:

not isnull({Times_DoctorName})

Of course this is depending on how your data is structured. If the row where the total is does not have a doctor name present, the above code will work. This should eliminate the totals data from your report but it shouldn't be an issue since the Cross-Tab provides you with a total regardless.

[Former Member · Accepted solution]

I don't want this row filtered out.  I want this "total" from the original Excel report to display in my report, and it is currently doing so correctly.

However, since it is generating a new column, I only want the zeros changed to blanks for display purposes.

All my calculations and data are correct otherwise.

Thanks.

[Former Member · Accepted solution]

Hi Warner,

The reason why I am filtering it out is because you can total up the data using a Summary.

Also since you are importing the total from the excel sheet, the total on the CrossTab is going to be skewed (Be twice the amount) since it is also summing up the total on the excel sheet. Moreover, The lower right hand corner of the Cross-Tab will provide you with a total of the data in the Cross tab.

If you want a total summary on the body of your report and outside of the CrossTab you can just navigate to Insert>Insert Summary.

To my knowledge, there is no way to hide/suppress just one data-set on a CrossTab and even if there were, your CrossTab summary would be skewed which would make the defeat the purpose of a total on the CrossTab.

In my opinion, it is best to filter out the total in the excel sheet and just total up your numbers inside Crystal Reports itself.

[Former Member · Accepted solution]

I tried your formula.  It took my extra column and zeros out.

HOWEVER, my cells are all "averages" (a formula field that is an average), so when I try to use the "total" function of the crosstab, it gives me an average instead of a sum.  This is why I need to import my "total" from the Excel spreadsheet instead of having Crystal Reports calculate it.

If I try to change my "total" in the crosstab to a "sum", it changes all my other averages to a sum as well, which is incorrect.

I would like to leave my "Grand Totals" suppressed and if I have an extra column with zeros and the "correct" total (sum) in the last cell, then that is okay.

All I really am asking is to change the "display" from 00.00.00 to a blank for aesthetics only.  My data is currently correct otherwise.

My Manager really needs this report this afternoon, and all he is asking is for me to delete the zeros.  If there is any way I can do this, that would be great.

Appreciate the help.

Thanks.

[Former Member · Accepted solution]

Hi,

You can just place a summary in the report via Insert>Insert Summary. That way you have a SUM total outside of the CrossTab. It doesn't make sense to have a CrossTab contain nothing but averages but has a Total as a sum.

[Former Member · Accepted solution]

I'm sorry.  There's confusion here because my report set-up isn't the norm, and I didn't fully explain it.

I have an Excel data sheet that lists every time a patient is in any room in our clinic.  However, not every patient will be in "every room" during a visit (certain rooms are for different procedures).

We are tracking "what is the average time patients are in each room".  We also tracking the "average total time a patient's visit takes at our clinic".  How we get the data for these two figures is very different.

Using my Excel spreadsheet, I am having Crystal (with an average formula) find out the average time a patient is in "each room".  These are the averages showing up in the cells of my crosstab.

However, we also want the "average total time" a patient spends at our clinic, per day, no matter which room(s) they were in.  This total has been calculated in the Excel spreadsheet as a separate row under each patient's room time information.  So, I have Crystal find this "total" and just add it as a new line in my Crosstab, which it is doing perfectly.

Just struggling with the look/format.  I now have an extra column of zeros with the "total" in the very last tab.  I'm doing a workaround today of just exporting to pdf and using Acrobat to erase the zeros.

Hope this makes sense.

[former_member292966 · Accepted solution]

Hi,

There are two ways to do this:

If you won't be using the crosstab to total, change your formula to return a string like:

if length ({Times_.TimeIn}) > 10 then

    cStr (cDateTime (left ({Times_.TimeIn},instrrev ({Times_.TimeIn},":")-1)))

Else "";

A formula has to return the result as a single datatype.  So I added the cStr function to convert the date time to a string and in case there is no valid datetime it returns an empty string.

This may cause problems because now your rows will be sorted as a string and not datetime.

The other way to do this is to format the field display by right clicking your date and going to Format Field.

In the Common tab you will see the Display String editor.  In there use the formula like:

If GridRowColumnValue ("Times_.TimeIn") = "00.00.00" Then

    ""

Else CStr (GridRowColumnValue ("Times_.TimeIn"));

This will keep the sort but change the way the cell appears.

[Former Member · Accepted solution]

He can also get rid of the label by right clicking on the label on top  - > Font - > Color Formula

if GridLabelAt("Times_.TimeIn",CurrentColumnIndex) = DateTime (0, 0, 0, 0, 0, 0) then

    crWhite

else

    crBlack

As for removing the prevailing 0's underneath I have no idea how to do this besides removing the data itself.

[Former Member]

Hi C Warner,

You can always create a new formula and cast the string into a date.

cdate({Your Excel Field})

[Former Member]

I tried your formula, but I received this error when running the report:  "Bad Date Format String"

This is my formula:  cdate({Times_.TimeIn})

This is the time date format of the Excel data:  Oct  1 2014  8:00:02:990AM