topic Re: Text to Date Format NULL when converting in App Development

Text to Date Format NULL when converting

QlikBeginner1 — Fri, 04 Mar 2022 10:27:54 GMT

Hello,

this may be simple,

I have two date fields, one which is text (DateTermIssued) and one which is the renewal date.

I am trying to get a duration of the two dates, but I am unable to format the DateTermIssued field to be a date field?

I have tried many different date functions to try and convert it, but unable to do so?

Date(Floor(Date#( DateTermIssued1, 'MMM DD YYYY HH:MM:tt'),'DD/MM/YY')) as an example, just gives a null, can you see what I am doing wrong,

Jun 1 2021 12:00AM

This is how the date term issued field is showing the data in excel. MMM DD YYY HH:MM:tt

any help would be greatly appreciated,

Re: Text to Date Format NULL when converting

hic — Fri, 04 Mar 2022 10:31:51 GMT

Try this in the script:

Date(Floor(Date#( DateTermIssued1, 'MMM DD YYYY hh:mmTT'),'DD/MM/YY'))

Note that hours and minutes must be in lower case, and TT must be in upper case.

Re: Text to Date Format NULL when converting

hic — Fri, 04 Mar 2022 10:36:02 GMT

A second alternative is
Date#(Left(DateTermIssued1,Index(DateTermIssued1,' ',3)-1), 'MMM DD YYYY')

Re: Text to Date Format NULL when converting

QlikBeginner1 — Fri, 04 Mar 2022 10:54:40 GMT

Thanks Henric,

Not working correctly, first formula NULL and the second formula gives me a result, but when i try to Format this into a date it nulls too,

Re: Text to Date Format NULL when converting

hic — Fri, 04 Mar 2022 11:10:22 GMT

A bracket was misplaced, and it needs to be D and not DD.

Try
Date(Floor(Date#(DateTermIssued1, 'MMM D YYYY hh:mmTT')),'DD/MM/YY')
Date#(Trim(Left(DateTermIssued1,Index(DateTermIssued1,' ',3)-1)), 'MMM D YYYY')
Date#(Trim(Subfield(DateTermIssued1,':',1)), 'MMM D YYYY hh')

Re: Text to Date Format NULL when converting

QlikBeginner1 — Fri, 04 Mar 2022 11:25:32 GMT

Thanks Henric, The first solution worked.

Date(Floor(Date#(DateTermsIssued, 'MMM DD YYYY hh:mmTT')),'DD/MM/YYY') as DateTermIssued1,
Date(Floor(Date#(DateTermsIssued, 'MMM D YYYY hh:mmTT')),'DD/MM/YYY') as DateTermIssued2,

Because some of the day values have one and two numbers, ive had to use this IF to bring through the above dates

=IF(ISNULL(DateTermIssued1),DateTermIssued1,DateTermIssued1)

Thanks Again!!!