Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Anonymous
Not applicable
Author

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?

Anonymous
Not applicable
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

Anonymous
Not applicable
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()

Anonymous
Not applicable
Author

Thanks!

Anonymous
Not applicable
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...