Date Conversion strange behaviour

[#1]

Hi,

I'm using the script below to convert the dates in the attached excel sheet.

LOAD Name,

date(alt(date#([date] , 'YYYY/MM/DD' ),date#([date], 'MM/DD/YYYY' ),date#( [date] , 'MM/DD/YY' ),date#([date], 'DD/MM/YYYY' ),

     date#([date], 'MM/DD/YYYY'),date#([date], 'M-DD-YY' ),Date#([date], 'DD/MM/YY'),

     date#([date], 'MM-DD-YY' ), num#([date], 'M-DD-YY' ),0))  as date

FROM

test.xlsx

(ooxml, embedded labels, table is Sheet1);

Please note that although the format 'YYYY/MM/DD'  is not present in the excel sheet but still i'm using it as this format used to be there in an earleir version of the file & could be there in future also.

The problem is the conversion is correct for every name exect for name: I for which I get :

13/02/2028

13/04/2030

12/06/2030

13/01/2031

13/03/2031

My set/default date format in the file is DD/MM/YYYY.

When i remove date#([date] , 'YYYY/MM/DD' ) from the alt then it works fine.

Why is this happening ??

Regards

[#2]

Hi Rob,

Thanks for having a look at this.

I understand what you said but I've a question:

Suppose the file also has some dates formatted as :12/06/30 (YY/MM/DD i.e. 30 June 2012)

then if we use : "DD/MM/YYYY" earflier, this date will be formatted as 12/06/30 (12 June 2030).

So,how to deal with this ?

Thanks again

[swuehl]

Well, how can you as a human being tell which format to apply when parsing in?

Is there any other information in the record that gives you a hint?

[#4]

Hi Stefan,

I just look at the different formats in the file and apply the alt function without considering the order of the formats.

I think your answer suggests that it can't be solved simply by using QV logic/functions and some human intervention has to be used.

What i can do here is ask the people who fills the date to use one of the format and not both.

Am i correct ?

Regards

[swuehl · Accepted solution]

Given your two formats in your example, 12/06/30 is a valid value in both date formats. So there is an ambiguity if you want to get the 'true' date.

It's always a good thing if your input data is in a well defined format, so if you can get your users to use one format, that would be much better than guessing around when reading in, right?

If possible, use a four digit year format. QV will use some AI to determine the full four digit year (e.g. determine 2013 from 13). I believe it will interprete 63 - 99 as being in 20th century, and 0 to 62 in 21st century (range is +-50 years from today).

Maybe this is according to what your users expect, maybe not.

And finally, there is an ISO standard for dates, which avoids the DD/MM/YY(YY) resp. MM/DD/YY(YY) etc. ambiguity altogether: 'YYYY-MM-DD'

[#6]

Hello Stefan,

As always your answer is an authority for me to ask the users to fill in a single date format.

Should I finally ask for the ISO standard: YYYY-MM-DD ?

then in the QV application I can simply use:  date(alt(date#(DateField,'YYYY-MM-DD'),0)

0 for date when the field is blank.

Is this setup ok ?

Regards

[#7]

Hey,

This kind of issues occur whn working on Excel sources, u have gt lots of expert advices,

I have my HR analysis partially dependent on excel files, my personal experience says put the blame on the users and demand them to follow one file / column format, tht is the only option u have with u

or else u work on it andf stable the files once received atleast for date column issue.

Rest good luck.