How Do I Calculate Elapsed Time from 2 Date/Time Stamps Fields?

Previous Member
Previous Member
500 Up Votes 500 Likes 500 Comments Second Anniversary
in Platform

Greetings,

 

I'm currently trying to capture the elapsed time between two Date/Time stamp fields from a table in SQL and break it out by Days/Hours/Minutes.  I've searched the content available on the BOARDville forums and have tried several ETL functions and can successfully calculate the days.  However, taking the remainder and calculating the hours and minutes have been problematic.  I've also tried converting the Date/Time fields to the serial number, taking the difference and convert back to a Date/Time.  The Mod functions doesn't seem to work either. 

 

Below are a couple screen shots to help illustrate the construct thus far:

The formula in the above figure ETL is: =IF(C6=0,0,DATEDIF(C5,C6,"d"))

This ETL below calculates the Serial Value of the Date/Time difference:

 

The Days Block determines elapsed Days.  The Hours Block algorithm is meant to take the remainder after the Days are subtracted and convert that remainder back into back Hours.

 

I'm in a holding pattern at this point so any assistance with this is greatly appreciated.

 

Thank you!

Ford Reid

Answers

  • Previous Member
    Previous Member
    500 Up Votes 500 Likes 500 Comments Second Anniversary
    edited March 2020

    Hi Ford, 

     

    In order to calculate the hours, I tried to replicate your situation as below.

    I hope that it is not too simplified and that it can help you.

     

    Source file structure (.csv):

    image

     

    I created two cubes, type Date, where we can store WL Date/Time and WL Date Assigned

    Then i applied the below algorithm in the layout:

    image

     

    Kind Regards,

     

    Alessio

Categories