Solved: Re: Transform a Datetime in Time greater than 24 H... - Qlik Community

Anonymous · ‎2016-05-09

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.

MarcoWedel · ‎2016-05-09

Hi,

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

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:

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

Anonymous · ‎2016-05-09

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 · ‎2016-05-09

How is the number getting converted to 101:02?

Anonymous · ‎2016-05-09

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 · ‎2016-05-09

Hi Sunny,

It's a Excel Time Format...

4 Days + 05:02 ...

sunny_talwar · ‎2016-05-09

Try this:

Table:

LOAD *,

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

LOAD * Inline [

Datetime

04/01/1900 05:02:08

];

sunny_talwar · ‎2016-05-09

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 · ‎2016-05-09

Interval()

Anonymous · ‎2016-05-09

Thanks!

Anonymous · ‎2016-05-09

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...

Transform a Datetime in Time greater than 24 Hours