Convert Day and time to Day and Hours

[qlikwiz123]

I am currently using the below expression to find the difference between two date/timestamps.

Interval(Max([Date1])-Max(Date2),'hh:mm:ss')

This gives the difference between the dates in the specified format. Is there any way I can show the output in the below format?

0 Days 18:23:00

1 Day 17:35:05

3 Days 12:09:44

...

...

[vishsaggi]

May be try this?

= Interval(Max([Date1])-Max(Date2),'d hh:mm:ss')

[qlikwiz123]

Hi Vishwarath,

I did try it but it gives the output as

0 18:23:00

1 17:35:05

3 12:09:44

Which is a bit confusing given it only shows a numerical value instead of saying how many days it is.

Anyway to show this as

0 Day(s) 18:23:00

1 Day(s) 17:35:05

3 Day(s) 12:09:44

?

[vishsaggi · Accepted solution]

May be try this?

= Interval(Max([Date1])-Max(Date2),'d') & ' Day(s) ' &Interval(Max([Date1])-Max(Date2),'hh:mm:ss')

[qlikwiz123]

Haha, this is clever. Thank you Vishwarath. I thought using the same expression twice with different formats would mess up the values. This works perfect in Straight Table. Thanks a lot. But I see that this is not an ideal way when you are showing the values in a Bar Chart. It only shows up the Day(s) values on the Axes but not the Day+hh:mm:ss values.

[vishsaggi]

Yes because we are using this Interval function twice to get the text value. I would suggest you to use the same format either in your script or if you are using this as expr use like Interval(Date2-Date1, 'd hh:mm:ss') and label your chart with something like Days. 

[qlikwiz123]

Thanks Vishwarath! Really appreciate your help

[vishsaggi]

Glad to help.