Converting Decimal Format to Days, Hour, minute format

austinisenhart

I'm trying to convert these DATEDIF values from this format that outputs "3.36 days" to the format that reads "3 days, 8 hours, 38 minutes". I would need to be able to average them out too so I don't think a CONCAT function would work. Where should I start?

Manasi_Panov

Hi @austinisenhart,

You can convert 3.36 into separate columns for Days, Hours, and Minutes (e.g., "3 days, 8 hours, 38 minutes"). However, representing the three numbers (3, 8, 38) in a single column without using a string doesn't make any sense. Consider averaging the DATEDIFF as the initial value (3.36) and then presenting the results in a table or chart, including the formatted breakdown ("3 days, 8 hours, 38 minutes") as a Tooltip for clarity.

Here are the Beast Modes I've created:

DATE_DIFF:
ROUND(((UNIX_TIMESTAMP(DATE_2) - UNIX_TIMESTAMP(DATE_1)) / 60 / 60 / 24) - 0.5, 2)

Days:
FLOOR(DATE_DIFF)

Hours:
FLOOR(MOD(DATE_DIFF * 24, 24))

Minutes:
FLOOR(MOD(DATE_DIFF * 1440, 60))

Now if you need to average for ID 'A', average the 191.78 and 223.43 first, and then extract Days, Hours, and Minutes.

DATE_DIFF_AVG:
AVG(DATE_DIFF) FIXED (BY ID)

You can present this info as a Tooltip for the graph:

TOOLTIP
CONCAT(FLOOR(DATE_DIFF), ' Days, ', FLOOR(MOD(DATE_DIFF * 24, 24)), ' Hours, ', FLOOR(MOD(DATE_DIFF * 1440, 60)), ' Minutes, ')