Calculating Time formula

[thelegazy]

I need some help in calculating the time difference between two activitities. I have the formula how to calculate it, but sometimes I get a negative number and it shows up as #####, is there anyway I can change that? I am planning to present the data via a pivot table, but it just gives me #### for values when I know there are positive times, but because of one negative time it gives me an entire #####. I have attached the sample sheet if anyone would like to take a look at it. Any help would be appreciated.

[FDibbins]

excel doesnt like to work with negative times.

If yo want to show "nothing" for negative times, try this...
=IF(SUM(INT(G3-G2)*24,MOD(G3-G2,1)*24)/24<0,"",SUM(INT(G3-G2)*24,MOD(G3-G2,1)*24)/24)

[thelegazy]

excel doesnt like to work with negative times.

If yo want to show "nothing" for negative times, try this...
=IF(SUM(INT(G3-G2)*24,MOD(G3-G2,1)*24)/24<0,"",SUM(INT(G3-G2)*24,MOD(G3-G2,1)*24)/24)

Thank you for the formula, but when I go to make a pivot table it still shows "#####" for the blank values. Is there anyway I can get rid of them or replace them with a 0:00. I know it will skew the average, but it shouldn't skew it that much. Any help would be appreciated.

[FDibbins]

change the "" to 0

=IF(SUM(INT(G3-G2)*24,MOD(G3-G2,1)*24)/24<0,0,SUM(INT(G3-G2)*24,MOD(G3-G2,1)*24)/24)

[thelegazy]

change the "" to 0

=IF(SUM(INT(G3-G2)*24,MOD(G3-G2,1)*24)/24<0,0,SUM(INT(G3-G2)*24,MOD(G3-G2,1)*24)/24)

Sorry, I'm embarrassed I asked that. Too late at night.

I think I found the problem though, for some values it will give me a value such as "12:03:16 AM" which will translate to "0:03:16", but then there will be another cell which will show "1/20/1900 6:58:41 PM" in the formula bar and display "498:58:41", but on the pivot table it will come up as ####. It looks like if it goes above a certain amount of hours it will switch to the second format. Is there anyway I can keep it as the initial format?

Does it matter if the cell format number is "[h]:mm:ss"?

[FDibbins]

yup, it is getting late

can you show me what you have so far?

formatting (unless its text) should not really matter that much, most formatting is purely cosmetic anyway, it does not alter the actual cell content, just how it gets displayed

[thelegazy]

yup, it is getting late

can you show me what you have so far?

formatting (unless its text) should not really matter that much, most formatting is purely cosmetic anyway, it does not alter the actual cell content, just how it gets displayed

So I put in the formula you gave me, and found the difference between the two cells which when I look into the formula bar shows "1/20/1900 6:58:41 PM", but when I look at the cell itself (which is formatted as [h]:mm:ss) it shows 498:58:41. When I look at another difference it will show "5:23:57 AM" in the formula bar and when just looking at the cell it will show "5:23:57". I've applied the same number formatting to them both so I don't know why it is showing something different in the formula bar. I assume because it took more than 24 hours? Sorry I don't have a sample sheet its on my original worksheet.

[FDibbins]

OK, can you explain to me exactly what you are trying to do here?

(also, your upload in post #1 did not contain any PT?)

[thelegazy]

OK, can you explain to me exactly what you are trying to do here?

(also, your upload in post #1 did not contain any PT?)

Damn I was trying to replicate the problem in the original file I posted, but I can't seem to get the error. I'm trying to calculate the time it takes to go from queueid to another.

Would you happen to know another formula that calculates time differences?

[FDibbins]

why not just use...
=IF(G3<G2,0,(G3-G2)*24)

[thelegazy]

why not just use...
=IF(G3<G2,0,(G3-G2)*24)

Hey you, you are awesome!!!!! Can't believe it was that simple. Really really appreciate your help and persistance.

[FDibbins]

More often than not, simpler is better

Happy to help and thanks for the feedback