DATEDIF in Days, Hours, and Minutes




  • Bob Forrester

    Really easy , all you need is  TimestampDiff and Mod.

    First we have 2 times in different columns:

    Next we will use TIMESTAMPDIFF to find days apart:

     Then we will use MOD with TIMESTAMPDIFF to get the hours:

    Since we have already done days, this time we will take mod of 24 hours.

    Do the same thing with Minutes:

    And finally use Concat to put the values into any format you would like:

    Comment actions Permalink
  • Bob Forrester

    Or you can do it all in one column....


    Comment actions Permalink
  • Mario Valdes II

    Thanks, Mr. Forrester; your time and effort are much appreciated! Unfortunately, I'm currently on v7.1.12 and the TIMESTAMPDIFF function isn't available; I'll have to work with my boss to see if we're eligible for an upgrade to a more current version.

    Comment actions Permalink
  • Konsta Danyliuk

    Hello Mario.
    I guess it is also possible to perform a similar calculation without TIMESTAMPDIFF, using TIMESTAMP and MOD functions.

    To determine number of days between two date values:

    1. Convert both of them into a number of milliseconds with the TIMESTAMP function.
    2. Subtract to get the difference between two dates in milliseconds.
    3. Divide the result to 86400000 (# of ms in one day) and round down. 

    Column Days: INT(ROUNDDOWN((TIMESTAMP(#B)-TIMESTAMP(#A))/86400000))

    Use similar approach to get hours, minutes and seconds (if needed).

    Column Hours: INT(ROUNDDOWN((MOD(TIMESTAMP(#B)-TIMESTAMP(#A);86400000))/3600000))
    Column Minutes: INT(ROUNDDOWN(MOD(TIMESTAMP(#B)-TIMESTAMP(#A);3600000)/60000))
    Column Seconds: INT(ROUND(MOD(TIMESTAMP(#B)-TIMESTAMP(#A);60000)/1000))

    Finally, concatenate these four columns to get the complete result.

    CONCAT(#Days;"d ";#Hours;"h ";#Minutes;"m ";#Seconds;"s")

    Comment actions Permalink
  • Mario Valdes II

    Thank you, Mr. Danyliuk! That worked perfectly!

    Comment actions Permalink

Please sign in to leave a comment.