Altair RISE

A program to recognize and reward our most engaged community members

Nominate Yourself Now!

"Data import from Excel - date time conversion hell"

i've got dates in Excel that got wrong conversion in RM.

Example: In Excel I have 14.07.2007 23:59 (formatted as dd.mm.yyyy h:mm), when I load it into RM it turns into Sun Jul 15 02:59:59 EEST 2007. WTF? Time zone conversion? I'm lost. Please help.

P.S. I've got 3 date/time variables within one Excel example set...after loading into RM 2 got +3 hours, 1 got +2 hours...again WTF?

I've tried another spreadsheet with the same variables and got different time shifts for the same variables.

Find more posts tagged with

AI Studio

Excel

Accepted answers

All comments

JEdward

Hi d1m0s,

I had a similar issue with a file until it was pointed out to me the 14/07/2007 is formatted as dd/MM/yyyy and not dd/mm/yyyy as I was entering.
Could this be similar to your problem?

Regards,
JEdward

d1m0s

JEdward wrote:

Hi d1m0s,

I had a similar issue with a file until it was pointed out to me the 14/07/2007 is formatted as dd/MM/yyyy and not dd/mm/yyyy as I was entering.
Could this be similar to your problem?

Regards,
JEdward

Thanks for your reply JEdward, I tried dd.MM.yyyy h:mm in date format field of Read Excel operator...unfortunately same result...time shift added to my original Excel data...had to use Talend to do the ETL:(

haddock

Hi Folks!

Sometimes you need to consider the milliseconds - yes really! This issue popped up last March, here's a reference to the thread...

http://rapid-i.com/rapidforum/index.php/topic,3489.0.html

Hope that helps.

JEdward

Thanks Haddock, I'll keep that in mind.

Cheers,
JEdward.