How to convert d:hh:mm:ss to hours ?

[titli]

Hi All,

I have the following in every column as shown below.Could you please let me know the formala for calculating the hours between two dates with times


ColumnK2 ColumnL2
05/11/2010 12:03:00 05/11/2010 13:03:00

ColumnM2
=TEXT((IF(L2=0, NOW(),L2))-K2,"h:mm:ss") gives value 0:1:00:00

Column K contains a Bug open date and Column L has the Bug Close date (that's why a if check is there , if a bug is not yet closed and has 0 value)

This formula is wrking fine except for case where the columns have the following values:-

ColumnK :12/10/2010 12:10:00
ColumnL :15/10/2010 12:32:00
Column M gets value as 0:22:00 but there is a difference of 2 days

If i make the formula as =TEXT((IF(L2=0, NOW(),L2))-K2,"D:h:mm:ss" )

then only i get 3:0:22:00

But what I want in ColumnM is ColumnL minus ColumnK value in hours

I know there are formulaes such as INT((K2-L2)*24) but that just gives me an incorrect value.

Please let me know if someone has a solution.




Thanks in advance
Titli

[Mariostg]

Have you cinsidered the DateDiff() function:
SELECT DateDiff("n",#5/11/2010 12:3:0#,#5/11/2010 13:3:0#) AS MyMinutes