Date and Time to Time Only

[#1]

Help nothing works. I want to convert a date and time field to time only

The field I'm converting always looks like this  with dates as 00/01/1900 with the time after ie

  00/01/1900 00:01:03

  00/01/1900 10:05:13

  00/01/1900 15:29:22

  00/01/1900 20:44:52

nothing seems to extract the time only any ideas

[ashfaq_haseeb]

Hi look at the below code.

All of them working.

t1:

LOAD Time,

time(Time) as Time1,

Time(Frac(Time),'hh:mm:ss') as Time2,

Time(Time,'hh:mm:ss TT') as Time3,

Time(Time,'hh:mm:ss') as Time4,

Time(Time,'hh:mm:ss TT') as Time5

FROM

QVTime.xlsx

(ooxml, embedded labels, table is Sheet1);

Regards

ASHFAQ

[its_anandrjs]

Use this line to load the field as time

Time(Time,'hh:mm:ss TT') as TimeField

Let me know if not works.

[nam]

Hi if its a formatting problem then just use the subfield function that will get you the correct answer:

maketime(subfield(yourdatefield,':',4),subfield(yourdatefield,':',5),subfield(yourdatefield,':',6)) as Time

you can do that in the script and will work a treat

[its_anandrjs]

Try to load only this way

Load

Time,

Time(Time,'hh:mm:ss TT'),8),'hh:mm:ss')  as NewTime1,

Time(Left(Time(Time,'hh:mm:ss TT'),8),'hh:mm:ss')  as NewTime2 // Format you get hh:mm:ss

FROM

[QVTime.xlsx]

(ooxml, embedded labels, table is Sheet1);

[#6]

Arrrg this is driving me nuts... it doesn't work?

Ive noticed that in excel when the file is opened only 00/01/1900 appears but when you double click the cell then the full date and time appears

ie 00/01/1900 15:29:22

[tresesco]

That is because of formatting. Actual underlaying data is having timstamp with time. See my attached qvw in above post.

[#8]

Thanks so much for all the replies

For some reason my QlikView is showing personal edition and not licenced and so I cant open the QVW, Ive faulted the licence issue with our IT but its gonna take a little while to resolve, is there any thing you can post in here in the meantime.. ?

[MarcoWedel]

Hi Glenn,

using the time() function should be enough:

LOAD  Time(Time, 'hh:mm:ss') as Time
FROM [http://community.qlik.com/servlet/JiveServlet/download/550208-111495/QVTime.xlsx]
(ooxml, embedded labels, table is Sheet1);

If you can ensure that the date part of your excel data will always be 00/01/1900 then there is no need for any removing the integer part of your excel timestamp (as e.g. the proposed frac() function would do). The integer part of the numeric representation of your loaded timestamps would be 0 already.

hope this helps

regards

Marco

[its_anandrjs]

If you need time then change format in the excel as Time and save the excel file.

[#11]

it doesn't work?

Ive noticed that in excel when the file is opened only 00/01/1900 appears but when you double click the cell then the full date and time appears

ie 00/01/1900 15:29:22

For some reason my QlikView is showing personal edition and not licenced and so I cant open the QVW, Ive faulted the licence issue with our IT but its gonna take a little while to resolve, is there any thing you can post in here in the meantime.. ?