# Need to calculate difference between date/time fields

#### hollinan

##### New Member
I am trying to count the difference between two fields which are date/time to show if we met our service level agreements. However, I don't want to count non business hours.

For example

Cell G3 is the date customer wants to close the deal - 6/23/2008 9:00 AM

Cell I3 is actual closing date 6/23/2008 10:45 AM

I would expect to see (1:45)

Can anyone help? Thanks!

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### iliace

##### Well-known Member
It's just subtraction. If your time in is in C2 and your time out is in D2, the formula would be =D2-C2

#### hollinan

##### New Member
I tried that, but it doesn't give me a negative number. It just shows as #######. Also, this wouldn't account for non business hours would it?

#### iliace

##### Well-known Member
Yeah, it won't do that. You can use a custom number format, or you have to change the date system to 1904 dates.

If your formula would be =ABS(I3-G3) you can then format it as -h:mm or (h:mm) to display the negative value, although you have to be careful and realize that the cell value is actually positive, in subsequent calculations.

Replies
13
Views
601
Replies
20
Views
312
Replies
8
Views
149
Replies
1
Views
593
Replies
2
Views
363

1,191,697
Messages
5,988,164
Members
440,131
Latest member
EricMoz

### 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.

### Which adblocker are you using?

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

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