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