Time troubles importing from Excel

[Former Member]

Hi all,

I've got statistics in excel which display as:

43:27:54

(43 hours, 27 minutes, 54 seconds)

If you move onto the cell, its actually

1/01/1900 7:27:54PM

Now, this is a little strange to work with, but I get around it by multiplying the cell by 86400 (seconds in 24 hours) which gives it to me in seconds which I can then average, maximum etc.

However, when I take this into Crystal, the field is stuck as 'DateTime' (1/01/1900 7:27:54PM) which means I can't multiply or really do anything with it.

This is the way the software spits the information out and the last thing I want is to have to massage the data in excel first, before opening in Crystal..

Any ideas?

Cheers.

[Former Member]

Hi Kyle

If you require to extract just the time value part of the datetime field, you can use the below function

TimeValue(datetimefield)

This would extract the time value from the field.

If your requirement is different from this please let us know.

Hope this helps!!!

Regards

Sourashree

[Former Member]

Thanks for the reply.

Unfortunately that function will only give me 7:27:54PM not 43:27:54 which is the actual value.

Is there a way I can convert a date to a number? Or some way to make the 1/01/1900 7:27:54 PM display in seconds? (Which would be 156474)

Everything I've tried ends up with an error cos you can't apply arithmetic to fields Crystal dictates as DateTime..

Is there a way to override Crystal and force the field type to Number?

[Former Member]

Hi Kyle,

I did following steps at my end.

In Excel file took the same date field i.e. 43:27:54. It did showed me as 1/01/1900 7:27:54PM (Suppose this field is in cellA4 of Excel sheet)

In B4 I wrote a formula like this = (86400*A4) which gives output 156474 (86400= seconds in 24 hours)

Then created a report off this Excel file.

I got first field as a u201CDate Timeu201D and Second field as a number.

I think second field is what you are looking for. As this is a u2018Numberu2019 field you can perform arithmetic on this.

Hope this would be helpful.

Regards,

[Former Member]

Heya, thanks for the suggestion, but I'm really trying to avoid having to manipulate the data in excel first.

These reports are going to need to be run weekly and I'm dealing with at least 30 columns scattered across 7 spreadsheets that I'd need to convert.

I was really hoping for a solution wholly within Crystal..