Subtracting Hours Over 2 Days

[Atomiq]

I've been trying to find the proper formula to subtract time over two days. For example, hour many hours would it be if I started at 10:21PM and ended at 12:12AM? This should be 1 hour and 51 minutes. I'm not getting that, instead I get: -3:36

My start time in cell B4: 22:21:00
My end time in cell C4: 0:12:00

The formula is: 24*IF(B4>C4, C4-B4, 1+C4-B4)

Also, is it possible to have the result appear as 1.85 hours?


I'm using Excel 2002 v 10.2614

Any ideas?

 

[22868]

Atomiq,

This works for me:

Format cells B4 and C4 as time (1:30 PM)
Format desigated cel (example B8) as General and use

B8=24*IF(B4<C4; C4-B4; 1+C4-B4).

Input values should be entered as (for example) 23:12 to get 11:23 PM

I ran a couple of tests and it works fine.

Good luck

Pete on XL97

 

[barry houdini]

You can use

=24*(C4-B4+(B4>C4))

 

[Atomiq]

RE:

Thank you guys very much for the help. I appreciate taking your time to work on this for me.