I am using a formula to calculate the time taken and days taken to close a ticket. Using the following formula to calculate the time taken.

=IF(AND(OR(E2="Resolved",E2="Closed"),OR(C2="4-Low",C2="3-Medium",C2="2-High")), NETWORKDAYS(L2,O2),IF(AND(OR(E2="Resolved",E2="Clo sed"),C2="1-Critical"), IF(O2<L2,TEXT(O2+1-L2,"h:mm:ss"),TEXT(O2-L2,"h:mm:ss"))," "))

E2 refers to the status of the ticket
C2 refers to the criticality of the ticket
O2 refers to the Date and Time of Closure of the ticket
L2 refers to the date and time of assigning the ticket.


The requirement is this.

For criticality Low, medium and high, the time taken should be reported in days.

For criticality as critical, the time taken should be reported in hours.


The display is as desired for both Days and Time.

However, for the critical requests the time is getting converted into text and unable to perform any further calculations like arriving at the average time taken etc.,

Is there any other formula which performs the same calculation(for critical requests, the rest if fine), which will provide the time in the required format and still will let me perform calculations on the same.


EggHeadCafe - Software Developer Portal of Choice
Solution to the Annoying Flickering Problem When Updating Textboxes
http://www.eggheadcafe.com/tutorials...-annoying.aspx