Find out if something is NOT a valid date

[datanibbler]

Hi,

I have to load an Excel_file where in column A, I usually have a numeric code. I need that as a mapping_table.

The issue is, following the actual block of data comes a block of notes where in column A there is a date - the date that the note was entered, I guess. Never mind, I don't need those.

=> The question is, how can I find out and filter out the lines where I have a date in column A?

      The length of the codes differs from 1 to 7 digits.

       Between those two blocks of data, there are some blank lines.

Can anyone give me a hint? I guess I have to use the DATE#_function - but then, any number can  be interpreted as a date.

Thanks a lot!

Best regards,

DataNibbler

[hic]

Use the IsNum().

If it is recognized as a date, then it has a numeric value. E.g.

     IsNum(Date#( A, 'M/D/YY' ))

HIC

[MK_QSL]

For any Valid date if you write

=Num(Date)

you should able to get some numbers....

If the numbers are not appearing, either your date format is wrong or it may not be a date

[datanibbler]

Hi Henric,

that doesn't seem to work.

All the dates come through as five-digit numeric values.

The date_format in the script is set to 'DD.MM.YYYY'

<=> When i write the formula

>> = ISNUM(DATE#([field], 'DD.MM.YYYY') <<

=> I always get 0, regardless whether the record is a date or one of the codes I need.

Can you tell me what's wrong?

Thanks a lot!

[MK_QSL]

Try

=NUM(Date#(YourDateField,'DD.MM.YYYY')) as YourDateFieldNumFormat

[anbu1984]

Can you post sample excel file

[datanibbler]

Hi,

maybe I have been imprecise in my original post:

- ALL of the entries in column _A are seemingly numeric since the ISNUM() function in a filter didn't filter out anything
   => That returned both the codes and the dates.

- Using DATE# with the date_format that is set on the Main_tab of my script, I get the number I have everywhere - DATE#([field], 'DD.MM.YYYY') returns just what is in the field, nothing else.

The dates in the Excel_file are written in DD.MM.YYYY format, but they are displayed in QlikView as five-digit numerics. The codes vary in length - so I cannot say for sure that there might not be five-digit codes, too.

I can try to put together a fake_app, but then the error might or might not come across ...

Best regards,

DataNibbler

[hic]

Can you give an example of how these dates look? And an example of what the non-dates look like?

HIC

[datanibbler]

Sorry,

this problem just dissolved into thin air 😉

It turns out I was looking at the wrong column - I have to load a different one where the filtering will be easier.

Still, I attach a small fake_app I have put together. From the Excel it is easy to see what is a note - I don't need those. That's why I thought I had to filter. It would still be interesting to know how I could do that.

Thanks a lot!

Best regards,

DataNibbler

[avinashelite]

Hi DataNibbler,

I think few of the data is not in correct format, better you create a new date using MAKEDATE() function by extracting the number using left(), right and mid functions.