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:
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