Turning time sum into decimal if over an hour

[handysmurf]

OK I am figuring the difference in times and the result is a decimal. But if the result is over an hour I loose that time, I only end up with the minutes and loose however many hours. So ... I need to fix that.

Here is the existing formula...

=IF(OR($M11569="D",$M11569="P",$M11569="w"),VALUE(MID(TEXT($AE11569-$AD11569,"hh:mm:ss"),4,2))+VALUE(RIGHT(TEXT($AE11569-$AD11569,"hh:mm:ss"),2)/60),"")

TIA

 

[joeu2004]

Perhaps:

=IF(OR($M11569="D",$M11569="P",$M11569="w"),($AE11569-$AD11569)*1440,"")

That returns decimal minutes. For example, if the difference is 1h 2m 30s, the result will be about 62.5.

I say "about" because an infinitestimal difference could arise due to the binary arithmetic. If that matters, you might want to round to the second first.

=IF(OR($M11569="D",$M11569="P",$M11569="w"),ROUND(($AE11569-$AD11569)*86400,0)/60,"")

-----

PS.... Unrelated to the solution to your problem, for future reference, replace your text manipulations with MINUTE($AE11569-$AD11569) and SECOND($AE11569-$AD11569) in order to extract the minutes and seconds of the time difference (excluding the hours and days). For example, if the time difference is 12:34:56, MINUTE() returns 34 and SECOND() returns 56.

 

[handysmurf]

thanks ... will let you know if it works.

I have no idea about your post script comments .... I didn't write the original formula. While I would prefer to learn the formulas instead of just asking for them here not many have time to elaborate.

 

[handysmurf]

Works great Thanks!!