TAT Calculate in office hours

[online]

Hi Guys,

Please help me in TAT calculation

My office time is 930AM to 530PM

I have to calculate TAT within this time exculding weekend and holyday. i am using this formula

=(NETWORKDAYS(A2,B2)-1)*(J$3-J$2)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),J$3,J$2),J$3)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),J$3,J$2)

Where a2 start date , b2 end date and j2 office start time and j3 office off time
suppose
Start date A2 9/24/2009 11:23

End date B2

10/7/2009 12:18


when i am calculatin office time 9:00AM to 6:00 it give correct ans 9:55hrs. but when i change office time 9:30 to 5:30 it gives 00:55hrs.

[Bob Phillips]

If you format the result cell as [h]:mm you will see what it really gives you.

[online]

I do that but still give same result

[Bob Phillips]

I doubt it, if you format it correctly you will get

81:55 for 9:00-18:00

and

72.55 for 9:30-17:30

In other words, the time is more than one day, and you need to format it to show more than 24 hours.