# Date calculation error

#### excel?

##### Board Regular
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)

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Try
Code:
``=MOD(C4-B4,1)``

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

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

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

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.

Replies
5
Views
100
Replies
13
Views
259
Replies
21
Views
735
Replies
32
Views
459
Replies
5
Views
67

1,196,516
Messages
6,015,678
Members
441,915
Latest member
sm Hussaini

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back