Convert string to date

[mikegrattan]

I have a field called observation_time with string data like this:

Last Updated on Jan 16 2018, 8:45 am PST

I am trying to format it as a standard timestamp.  So far, I have used the following expression:

=Date#(textBetween(observation_time,'Last Updated on ', 'P'))

This finds the date and time information between the beginning and end of the string, so it now looks like this:

Jan 16 2017, 8:45 am

Although this is a step in the right direction, my next goal is to use the Max() function to get the last record of the data set.  The Max function won't work on this as it still thinks it's a string, and not a date.  I've tried wrapping it in the Date function but that results in an invalid dimension. (I'm testing functions in a table chart in Qlik Sense, but I will be using the script editor to load the data once I figure out how to convert the field).

Any ideas on how to convert this string to a valid date format so I can then use the Max function on the field?

Thanks.

[rubenmarin · Accepted solution]

Hi Mike, Date#() is used to specify the input date format, ie:

Date#('Jan 16 2017, 8:45 am', 'MMM DD YYYY, hh:mm tt')

Then you can use:

Date(Date#('Jan 16 2017, 8:45 am', 'MMM DD YYYY, hh:mm tt'))

Num(Date#('Jan 16 2017, 8:45 am', 'MMM DD YYYY, hh:mm tt'))

[mikegrattan]

That got me going in the right direction, the following is giving me the result that I want:

=timestamp(Date#(textBetween(observation_time,'Last Updated on ', 'P'),'MMM DD YYYY, hh:mm tt'))

Thank you very much.