Another Date Calculation Question

[#1]

I am new to QlikView and am using QlikView for Windows version 11.  I am having difficulting subtracting 1 date/time from a second date/time.  I am using a CSV file as my datasource.  Based on my abilty to concatenate the date/times, I believe the date/times are recognized as strings by Qlikview.

I have tried the timestamp#, date#, date functions but always get '-' for my answer.  I have perused the QlikView Reference Manual and the QlikView Time and Date primer documents found during my google searches.  I have tried converting the string into an actual date/time but have failed miserably.

Examples of my date/time data are:  3/20/2012 11:15  and 3/16/2012 8:47  (I have also uploaded my source file)

I have used the following formats to no avail:

(DATE([Logon time],'M/D/YYYY h:mm')) as LogonTimeDate

(DATE([Logon time],'M/D/YYYY hh:mm')) as LogonTimeDate

(DATE([Logon time],'M/D/YYYY hh:mm:ss')) as LogonTimeDate

(DATE([Logon time],'M/D/YYYY h:mm:ss')) as LogonTimeDate

(DATE([Logon time],'M-D-YYYY h:mm')) as LogonTimeDate

(DATE([Logon time],'M-D-YYYY hh:mm')) as LogonTimeDate

(DATE([Logon time],'M-D-YYYY hh:mm:ss')) as LogonTimeDate

(DATE([Logon time],'M-D-YYYY h:mm:ss')) as LogonTimeDate

Any guidance would be mucho appreciated!

Thanks,

John

[chematos]

Hi John,

try this and let me now what is the result

DATE(floor([Logon time]),'M/D/YYYY h:mm') as LogonTimeDate

Regards,

Chema

[#3]

Hi Jose,

I had not tried the floor function.  Unfortunately, I still get the same result the dash.

Thanks,

John

[jagan]

Hi John,

Try this

=Date(Date#('3/20/2012 11:15', 'M/DD/YYYY HH:mm'), 'MM/DD/YYYY HH:mm:ss')

Hope this helps you.

Regards,

Jagan.

[#5]

Thanks Jagan, however it did not work for me.  I have given up and converted by source file to an XLS and the timestamp# function works for me.  I think the issue with my CSV was that is did not have the AM/PM included. This is simply a guess...

Many thanks for the help!