How to get max date from formatted text date

[#1]

Hello,

See attached qv file. I have a mix field of both text and date in the field i wanna format to a standard date to pick the max(Date) i.e the latest date data only. from the newly formated date field. See QVW

Thanks

Ema

[ecolomer]

Can you upload excel file?

First, try to format column of Date as Date Format

[maxgro]

it seems the problem is you have this date format

SET DateFormat='M/D/YYYY';

but the date in excel is also in DD/MM/YYYY format (13/11/2015 DD/MM/YYYY)

you can try to change the date format

SET DateFormat='DD/MM/YYYY';

or without change in the SET DateFormat

use the date functions (Date and Date#) in the load; if you post the excel I could check

LOAD

     [S/N],

     Date as OldDate,

     Date(alt(num(Date), Date#(Date, 'DD/MM/YYYY'))) as Date,

     Group,

     Target,

     Actual

FROM (ooxml, embedded labels, table is [ summary]);

[HirisH_V7]

Hi,

Please check the attachment. (Max date on list-box)

Thanks,

hirish

[maxgro]

NewSummary:

NoConcatenate

LOAD [S/N],

  Date as OldDate,

     Date(alt(num(Date), Date#(Date, 'DD/MM/YYYY'))) as Date,

     Group,

     Target,

     Actual

FROM

[summary II - Copy.xlsx] (ooxml, embedded labels, table is [ summary]);

[Anonymous]

try Alt function