How to Compute down time for AMC calls

[REENA GUPTA]

Hi
I want to calculate total down time( Column I) of AMC call to implement SLA. Calculations of total down time are not coming correctly. Please guide me what formula to be used. Sample file attached

Regards

 

[Hui]

Reena

Your current formula is correct, it is the formatting of the Time that is incorrect

Using "h:mm:ss" as a format code will give you time in 24 Hr format
That is if something takes 27 hrs it will be displayed as 3:00:00

You want total time so use a format code of '[h]:mm:ss"

=TEXT(H3-G3,"[h]:mm:ss")

As none of the Dates/Times in Columns G & H have seconds, I'd also change column I to
=TEXT(H3-G3,"[h]:mm")

 

[NARAYANK991]

Hi ,

The formula to calculate the time difference between two times is a straightforward End Time - Start Time.

The displayed output will depend on the format chosen for the cell which contains the formula.

Choosing a custom cell format of [hh]:mm:ss is required to display hours in excess of 23.

The use of the TEXT function is unnecessary , since it converts a numeric value to text ; if you wish to use the total downtime in a summary report such as pivot table , it would make it more convenient to retain it as a numeric value.

Narayan

 

[Hareesh_KLD]

HI,

Pls check, workbook

tried to split difference of time into days and hours

Regards
Hareesh

 

[REENA GUPTA]

Reena

Your current formula is correct, it is the formatting of the Time that is incorrect

Using "h:mm:ss" as a format code will give you time in 24 Hr format
That is if something takes 27 hrs it will be displayed as 3:00:00

You want total time so use a format code of '[h]:mm:ss"

=TEXT(H3-G3,"[h]:mm:ss")

As none of the Dates/Times in Columns G & H have seconds, I'd also change column I to
=TEXT(H3-G3,"[h]:mm")

 

[REENA GUPTA]

Hi Hui

Thanks... a lot.

 

[REENA GUPTA]

Hi ,

The formula to calculate the time difference between two times is a straightforward End Time - Start Time.

The displayed output will depend on the format chosen for the cell which contains the formula.

Choosing a custom cell format of [hh]:mm:ss is required to display hours in excess of 23.

The use of the TEXT function is unnecessary , since it converts a numeric value to text ; if you wish to use the total downtime in a summary report such as pivot table , it would make it more convenient to retain it as a numeric value.

Narayan

 

[REENA GUPTA]

Hi

Thanks a lot !!!

 

[REENA GUPTA]

Hi Hui

I tried your solution. It is working fine in case u have both Start time & End time. But where u do not have End time (means u have not yet closed the AMC call), formula displays ####. Please suggest how to get rid of these #s.

 

[NARAYANK991]

Hi ,

Use an IF function to check for whether the End Time is available or not.

An example would be :

=IF(EndTime > 0 , TEXT(EndTime - StartTime,"[h]:mm:ss") , "")

Narayan

 

[REENA GUPTA]

Thanks Narayan ! It is working fine !