topic Convert HH/MM/SS into DD/HH/MM in QlikView

Convert HH/MM/SS into DD/HH/MM

Tue, 02 Jun 2015 08:55:27 GMT

Hello All,

I need to convert HH/MM/SS into DD/HH/MM (days/hour/minutes) in qlikview.

However, my 1 day should equal to 10 hours (working hours of the company) and not 24 hours.

Any suggestions please?

Many thanks for your help.

Kind Regards,

Hasvine

Re: Convert HH/MM/SS into DD/HH/MM

PrashantSangle — Tue, 02 Jun 2015 09:02:57 GMT

Hi,

convert your time into seconds and then divide it by 60*60*10 i.e. 36000 to get Day

regards

Re: Convert HH/MM/SS into DD/HH/MM

Tue, 02 Jun 2015 09:08:17 GMT

Try this...

=Hour('20:30:40')/10+Minute('20:30:40')/600+Second('20:30:40')/36000

Thanks,

Vinod Kumar V

Re: Convert HH/MM/SS into DD/HH/MM

rbecher — Tue, 02 Jun 2015 10:04:02 GMT

As I understand it right 1 day shoud represent 10 hrs but hours, minutes and seconds should be equivalent?

Try this:

=Interval(Hour('20:30:40')+Minute('20:30:40')/60+Second('20:30:40')/3600, 'DD:HH:MM')

Re: Convert HH/MM/SS into DD/HH/MM

swuehl — Tue, 02 Jun 2015 10:17:29 GMT

=dual( floor(interval#('22:35:40') / interval#('10:00:00')) & 'd ' & interval(fmod( interval#('22:35:40') ,Interval#('10:00:00')),'hh:mm'), Interval#('22:35:40'))

Take care of your format codes, 'MM' denotes month, not minutes.

Re: Convert HH/MM/SS into DD/HH/MM

rbecher — Tue, 02 Jun 2015 10:21:09 GMT

..had a mistake:

=Interval(Hour('20:30:40')/10+Minute('20:30:40')/1440+Second('20:30:40')/86400,'DD:hh:mm')

Re: Convert HH/MM/SS into DD/HH/MM

MayilVahanan — Tue, 02 Jun 2015 10:25:12 GMT

Try like this

=Date(Date#(Floor(Hour('20:30:40') / 10)&':'& Mod(Hour('20:30:40'), 10)&':'&Minute('20:30:40'),'DD:HH:mm'),'DD:HH:mm')

Re: Convert HH/MM/SS into DD/HH/MM

Tue, 02 Jun 2015 10:25:50 GMT

Hello !,

Thank you all for your help. The answer of swuehl is working great.

Thanks,

Hasvine

Re: Convert HH/MM/SS into DD/HH/MM

rbecher — Tue, 02 Jun 2015 11:09:25 GMT

..still not correct, but this (and much shorter):

=Interval(div(Interval#('15:30:40'),10/24)+fmod(Interval#('15:30:40'),10/24),'DD.hh:mm:ss')