Forum Discussion

Kratchie's avatar
Kratchie
Frequent Visitor
3 years ago
Solved

Calculate duration from fixed date/time

Hi,   I've a column with fixed time which begins from 2000-01-01 00:00:00 If we find 2000-01-02 03:10:10 in that column it means 27h, 10 min, 10 sec in duration. How can I calculate and get it as ...
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi Kratchie 

    not sure about your ultimate goal, try this:

    Column =
    VAR _d = TRUNC( [Duration]-DATE(2000,1,1))
    VAR _hms = FORMAT((([Duration]-DATE(2000,1,1))-_d),"HH:MM:SS")
    RETURN _d &" Days " &_hms
     
    I tried and it worked like this:
FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

Hi Kratchie 

 

try to create a column with this:

Column = FORMAT([Duration]-DATE(2000,1,1),"HH:MM:SS")
 
i tried and it worked like this:
  • Kratchie's avatar
    Kratchie
    Frequent Visitor
    3 years ago

    Hi FreemanZ 

     

    Your script works perfectly with hours only. When it's more than 24 hours like 1 day + 6 hour it only shows 6:00

     

    • FreemanZ's avatar
      FreemanZ
      Icon for Super User rankSuper User
      3 years ago

      hi Kratchie 

      not sure about your ultimate goal, try this:

      Column =
      VAR _d = TRUNC( [Duration]-DATE(2000,1,1))
      VAR _hms = FORMAT((([Duration]-DATE(2000,1,1))-_d),"HH:MM:SS")
      RETURN _d &" Days " &_hms
       
      I tried and it worked like this:
      • Kratchie's avatar
        Kratchie
        Frequent Visitor
        3 years ago

        This is perfect thank you FreemanZ