# 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
Did you mean:
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
MVP

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

17 Replies
Specialist

Hi,

try this to convert to hh:mm

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

...

from...

Hope it helps

MVP

How is the number getting converted to 101:02?

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

Contributor III
Author

Hi Sunny,

It's a Excel Time Format...

4 Days + 05:02 ...

MVP

Try this:

Table:

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

Datetime

04/01/1900 05:02:08

];

MVP

Slight update:

Table:

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

Datetime

04/01/1900 05:02:08

];

MVP

Interval()

Contributor III
Author

Thanks!

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