Converting minutes to Days:Hours:Minutes

Question & Answer
, , ,
#1

Hi Team ,

Hope someone can guide me to solve this. I have a calculated field with minutes:

Example: I have this sample field (in minutes)
65
126
1442

I need to convert this as:
1hour 5Minutes
2hour 6Minutes
1Day 2Hours

Anyone know how to get this in quicksight?

Thank You

Convert date to years, months and days
#2

Hi @Athi_Path -

QuickSight does not have a feature to format time duration.

Edit: Use the approach @darcoli posted :slight_smile:

2 Likes
#3

:smiley: was thinking if this could be done with the available date functions and came up with this. It basically adds the {minutes} to a date and then uses dateDiff to extract the periods:

Add a calculated field called {startDate} with definition:

parseDate('1970-01-01')

Add a calculated field called {endDate} with definition:

addDateTime({minutes}, 'MI', {startDate})

Finally add the calculated field {Minutes to periods} with definition:

concat(
  ifelse( datediff(startDate, endDate, 'DD') > 0, concat(toString(datediff(startDate, endDate, 'DD')) , ' days '), ''),
  ifelse( datediff(startDate, endDate, 'HH')%24 > 0, concat(toString(datediff(startDate, endDate, 'HH')%24) , ' hours '), ''),
  ifelse( datediff(startDate, endDate, 'MI')%60 > 0, concat(toString(datediff(startDate, endDate, 'MI')%60) , ' minutes '), '')
)
2 Likes
#4

@robdhondt Thank You very much for the workaround! It’s a great logic you did use to help with my requirement and it works. I learned some new things from your post! Appreciate it!

#5

@darcoli Thanks for this! Works perfectly!. I have been searching to find a solution for this for last couple of weeks now, and finally found a place! I Appreciate the help!