Date calculation error

[excel?]

I was given a Formula to calculate # of hours worked between 2 times but it is not working all the time. Columns B & C contain Times IN and OUT while Column D contains the following formula to calculate the # of hours. However when time crosses Midnight formula shows 0.

=IFERROR(IF(SUM(C4-B4)>0,IF(SUM(C4-B4)>0,(SUM(C4-B4)*24.005)-0.5,0),0),0)

Times are entered as:
IN= 2:30:00 PM OUT= 11:00:00 PM and calculates 8.0

But this doesn't calculate:
IN= 3:30:00 PM OUT= 1:30:00 AM It calculates 0.0

If there are no times entered, need the formula to return 0 but if time is entered, it should calculate the number of hours.

Any help would be appreciated. (Using Excel 2013)

 

[MarcelBeug]

Try

=MOD(C4-B4,1)

If result field is numeric, not time: multiply by 24.

 

[AhoyNC]

Based on your formula it looks like you are subtracting 1/2 hour off the total hours.
If so try:

=IF(AND(C4>0,D4>0),(MOD(D4-C4,1)*24-0.5),0)

 

[excel?]

Thank you both so much. I do need it to subtract the 1/2 hour. This formula is just what I need. I appreciate your help.