Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
gabriel_palucos
Contributor III
Contributor III

Transform a Datetime in Time greater than 24 Hours

It's possible to convert a Datetime for a Time format greater than 24 hours using Script ?

Ex.: 04/01/1900 05:02:08 -> 101:02

Thanks.

1 Solution

Accepted Solutions
MarcoWedel

Hi,

one possible solution could be to format your datetime excel column as Interval when loading in QlikView:

QlikCommunity_Thread_216139_Pic3.JPG

The difference between the 101:02  respective 125:02 interpretation is due to a Leap year bug‌ in Excel that considers 1900 as leap year despite it is not.

Software not sharing this Excel "feature" (e.g. QlikView) presents a date with decimal value 4 as 03/01/1900:

QlikCommunity_Thread_216139_Pic1.JPG

QlikCommunity_Thread_216139_Pic2.JPG

table1:

LOAD Timestamp(Datetime, 'DD/MM/YYYY hh:mm:ss') as Datetime,

    Num(Datetime) as decimal,

    Interval(Datetime) as Interval

FROM (ooxml, embedded labels, table is Sheet1);

hope this helps

regards

Marco

View solution in original post

17 Replies
marcarreras
Specialist
Specialist

Hi,

try this to convert to hh:mm

load ...

     floor(yourdatefield*24) & ':' &  floor(yourdatefield*24*60-floor(yourdatefield*24)*60) as yourNEWtimefield,

...

from...

Hope it helps

sunny_talwar

How is the number getting converted to 101:02?

gabriel_palucos
Contributor III
Contributor III
Author

Hi,

This formula give me 125 hours...

04/01/1900 05:02:08


It's a Excel Time...


4 Days + 05:02:08 Hours... = 101:02

gabriel_palucos
Contributor III
Contributor III
Author

Hi Sunny,

It's a Excel Time Format...

4 Days + 05:02 ...

sunny_talwar

Try this:

Table:

LOAD *,

  (Day(Datetime) * 24 + Hour(Datetime)) & ':' & Minute(Datetime) as Time;

LOAD * Inline [

Datetime

04/01/1900 05:02:08

];


Capture.PNG

sunny_talwar

Slight update:

Table:

LOAD *,

  (Day(Datetime) * 24 + Hour(Datetime)) & ':' & Num(Minute(Datetime), '00') as Time;

LOAD * Inline [

Datetime

04/01/1900 05:02:08

];

MarcoWedel

Interval()

gabriel_palucos
Contributor III
Contributor III
Author

Thanks!

gabriel_palucos
Contributor III
Contributor III
Author

The correct is 101:02

And if i have a date like this:

01/01/1900 11:01:52

This function doesn't work...

It's a hard problem with format value in Excel...