Need to calculate difference between date/time fields

[hollinan]

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.

Business Hours are 8-5.

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!

 

[iliace]

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]

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]

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.