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

Previous Member

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

Previous Member

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

 

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:

 

Kind Regards,

 

Alessio