Beast Mode

Beast Mode

Converting Decimal Format to Days, Hour, minute format

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

Best Answer

  • Coach
    Answer ✓

    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.

Answers

  • Coach
    Answer ✓

    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.

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In