Converting Decimal Format to Days, Hour, minute format

austinisenhart
austinisenhart Member
in Beast Mode

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?

image.png

Answers

  • Manasi_Panov
    Manasi_Panov Contributor

    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)

    image.png

    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, ')

    image.png

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

Categories