Excel formula to convert decimals to time

[Akhila Attili]

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="Closed"),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/tutorial...b2-326ca6f82d08/solution-to-the-annoying.aspx

 

[Bernie Deitrick]

You could use the calculate or the change event to change the number format of the cell with the
formula. For example, copy either of these codes right-click the sheet tab, and select "View Code"
and paste the code into the window that appears:

(For formulas in B2:B100)

Private Sub Worksheet_Calculate()
Dim myC As Range
For Each myC In Range("B2:B100")
If Cells(myC.Row, 3).Value = "1-Critical" Then
myC.NumberFormat = "[h]:mm"
Else
myC.NumberFormat = "0"
End If
Next myC
End Sub

or

(For formulaus in column B)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Then Exit Sub
If Target.Value = "1-Critical" Then
Cells(Target.Row, 2).NumberFormat = "[h]:mm"
Else
Cells(Target.Row, 2).NumberFormat = "0"
End If
End Sub

For either, you need to change the formula so that it doesn't generate text using TEXT. Change

IF(O2<L2,TEXT(O2+1-L2,"h:mm:ss"),TEXT(O2-L2,"h:mm:ss"))

to

IF(O2<L2,O2+1-L2,O2-L2)

Or even just

O2-L2+(O2<L2)*1

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Ooops... the second event code should have

If Target.Cells.Count > 1 Then Exit Sub

as the first line.

HTH,
Bernie
MS Excel MVP

You could use the calculate or the change event to change the number format of the cell with the
formula. For example, copy either of these codes right-click the sheet tab, and select "View
Code" and paste the code into the window that appears:

(For formulas in B2:B100)

Private Sub Worksheet_Calculate()
Dim myC As Range
For Each myC In Range("B2:B100")
If Cells(myC.Row, 3).Value = "1-Critical" Then
myC.NumberFormat = "[h]:mm"
Else
myC.NumberFormat = "0"
End If
Next myC
End Sub

or

(For formulaus in column B)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Then Exit Sub
If Target.Value = "1-Critical" Then
Cells(Target.Row, 2).NumberFormat = "[h]:mm"
Else
Cells(Target.Row, 2).NumberFormat = "0"
End If
End Sub

For either, you need to change the formula so that it doesn't generate text using TEXT. Change

IF(O2<L2,TEXT(O2+1-L2,"h:mm:ss"),TEXT(O2-L2,"h:mm:ss"))

to

IF(O2<L2,O2+1-L2,O2-L2)

Or even just

O2-L2+(O2<L2)*1

HTH,
Bernie
MS Excel MVP

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="Closed"),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/tutorial...b2-326ca6f82d08/solution-to-the-annoying.aspx