Hours to Day and Hour Format Conversion

[Sara1]

Hi! 

I'd like to convert any hours over 72 hours to day & hour format as in the last two columns in the following chart below. Any guidance would be greatly appreciated. Thank you!

Max(timestamp)	now()	round((now() - max(timestamp)) * 24)	(now() - max(timestamp)) * 24	Desired output	Desired output 2
6/14/2022 12:01	6/14/2022 17:55	6	5.895277778	6	6
6/14/2022 10:02	6/14/2022 17:55	8	7.868611111	8	8
6/14/2022 10:02	6/14/2022 17:55	8	7.871111111	8	8
6/13/2022 20:01	6/14/2022 17:55	22	21.885	22	22
6/12/2022 20:00	6/14/2022 17:55	46	45.90194444	46	46
6/11/2022 5:01	6/14/2022 17:55	85	84.89972222	3 days 13 hours	3:13
6/6/2022 14:25	6/14/2022 17:55	195	195.4952778	8 days 4 hours	8:04
6/1/2022 13:04	6/14/2022 17:55	317	316.8405556	13 days 5 hours	13:05
5/18/2022 1:00	6/14/2022 17:55	665	664.9108333	27 days 17 hours	27:17
4/27/2022 15:59	6/14/2022 17:55	1154	1153.917222	48 days 2 hours	48:02

[chaorenzhu · Accepted solution]

use div() and mod()

if([round((now() - max(timestamp)) * 24)]>72,div([round((now() - max(timestamp)) * 24)],24)&' days '&mod([round((now() - max(timestamp)) * 24)],24)&' hours',[round((now() - max(timestamp)) * 24)])

[rwunderlich · Accepted solution]

Here's another solution. The proposed div() and mod()solution by @chaorenzhu  is probably easier to read, but this one was fun to write 🙂

if (round([now()] - [Max(timestamp)], 1/24) < 3
,Interval(round([now()] - [Max(timestamp)], 1/24), 'h')
,Replace(
Replace(
Interval(round([now()] - [Max(timestamp)], 1/24), 'd x h z')
,'x', 'days')
,'z', 'hours')
) 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[Sara1]

Awesome thank you so much! This is perfect.

[Sara1]

Thank you very much! I'm fortunate to have not one but two working solutions! 🙂