Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel formula to convert decimals to time
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel formula to convert decimals to time
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 <Akhila Attili wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel formula to convert decimals to time
Ooops... the second event code should have
If Target.Cells.Count 1 Then Exit Sub as the first line. HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 <Akhila Attili wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I have excel convert fractions to decimals? | Excel Worksheet Functions | |||
i want to convert decimals into time format | Excel Discussion (Misc queries) | |||
Convert decimals into mm in excel spreadsheet | Excel Worksheet Functions | |||
convert time in decimals to minutes | Excel Discussion (Misc queries) | |||
Convert Seconds and decimals to time | Excel Discussion (Misc queries) |