Time and Time Formatting

[santeria]

Currently, I have a Data Extraction that has a Slight (?? ) problem with time, and time Formatting.
The Relevant Data Lines Affect 2 Columns, but only because the Second Column has a SUM formula.

All other Summaries work great, however G2 on the First Summary sheet of the file persists in presenting time in Seconds, so for the 15th of October , the Average Handle time is 637 seconds, and the same occurs for G6 where the summary is 629 seconds; I can't format these cells using any standard selected procedure.
When Transferred to the Second Monthly Summary file, 637 Seconds, set as a Number, becomes 14:48 in time, and the other line becomes 0:00 when set to time format, but reverts to 629 when Changed to Number format.

The Summary Sheet for the Bosses, a Third file, Presents everything else perfectly except that the 14:48 is inherited in the AHT column in the "Skill 77" Tab, and the time formatted AHT column on tab "17 Skill" goes to 0:00 and so the inherited time as seconds, which refuses to be formatted to Minutes and seconds transfers over.

Is there a formula I need to Apply to change the Seconds to Minutes, or something which will Correct the Data Transfer problem.

Ta

 

[santeria]

To your address that is on the clickable link.

Mine is boucaria@comcast.net

Ta

I can't find your email address. Where did you send it?

 

[Chitosunday]

This is the final formula. I already emailed the sample and should be format of your input which is like 22:02

Public cva, cvb, cel, oper As String
Public cvfina, cvfinb As Long

Function mmss(a As String) As String
Application.Volatile
Dim i As Integer
cvfina = Empty
cvfinb = Empty
For i = 1 To Len(a)
tem = Mid(a, i, 1)
Select Case tem
Case "+", "-"
If Not IsEmpty(cel) Then cnvrt
oper = tem
cel = ""
Case Else
cel = cel & tem
End Select
Next i
If Not IsEmpty(cel) Then cnvrt
If cvfina < 0 And cvfinb > 0 Then
Do
cvfinb = cvfinb - 60
cvfina = cvfina + 1
If cvfinb = 0 Then Exit Do
Loop Until cvfina >= 0
End If
If cvfinb < 0 And cvfina > 0 Then
Do
cvfinb = cvfinb + 60
cvfina = cvfina - 1
If cvfina = 0 Then Exit Do
Loop Until cvfinb >= 0
End If

If Abs(cvfinb) > 60 Then
cvfina = cvfina + Fix(cvfinb / 60)
cvfinb = cvfinb Mod 60
End If
If cvfinb < 0 And cvfina >= 0 Then
cvb = Format(Abs(cvfinb), "00")
cva = "-" & cvfina
Else
cvb = Format(Abs(cvfinb), "00")
cva = Format(cvfina, "0;-0")

End If
cel = Empty
oper = emtpy
tem = Empty
cvfina = Empty
cvfinb = Empty
i = Empty
mmss = cva & ":" & cvb
cva = Empty
cvb = Empty
End Function

Sub cnvrt()
Dim i As Integer
Dim cval, ctem As String
Dim flg As Boolean
cva = ""
cvb = ""
flg = True
cval = Range(cel).Value
For i = 1 To Len(cval)
ctem = Mid(cval, i, 1)
If ctem = ":" Then
flg = False
Else
If flg = True Then
cva = cva & ctem
Else
cvb = cvb & ctem
End If
End If
Next i
If oper = "+" Or oper = "" Then
cvfina = cvfina + Val(cva)
cvfinb = cvfinb + Val(cvb)
Else
cvfina = cvfina - Val(cva)
cvfinb = cvfinb - Val(cvb)
End If
cval = emtpy
ctem = Empty
i = Empty
End Sub



Sub cnvrtformula()
Dim xsel, xform As String
For Each sel In Selection
xform = sel.Formula
xsel = Left(xform, 1)
xform = Right(xform, Len(xform) - 1)
Select Case xsel
Case "=", "+", "-"
If Not IsNumeric(Mid(sel, 2, 1)) Then
sel.NumberFormat = "General"
sel.Formula = "=mmss(""" & xform & """)"
sel.HorizontalAlignment = xlRight
End If
End Select
Next sel
End Sub

 

[santeria]

Okay, thanks for this.
Does this affect the time Issue of AM and PM display ?
Or does it facilitate the manual input changes ??i.e. is the macro something which enables the instructions you gave me for manual input?

The issue I have tried to fix is that the data import currently accurately brings in and places raw data in time format.
My concern is , if time data comes in, does it go to text when displayed? And it appears that the final total does solve the calculation issue.

One scenario, suppose the VP works with the sheet, I mean actually tries calulation... will that be a problem in your solution ??

In essence, is the macro and the steps you gave me a workaround, or does it actually remove the AM and PM? and I am not seeing that...
In any event, it seems to be the key to what is needed, in one sense.


Ta

 

[Chitosunday]

My formula is for text displayed time. Text displayed does not display the am/pm unless you type it. What i want to know is what is the data resulting from the actual import. That i like to see. Can you email it to me. The manual instruction is for the short cut of converting your old formula to
my new formula which is =mmss(" ") . Just select the cells you want to convert the formula as the old formula was written
as +d9-d10-d11 which will be convert to =mmss("d9-d10-d11") when you pressed ctrl f (formulated when you go to tools, macro , click cnvrtformula then option then put F as your short cut key the OK.

 

[santeria]

The data I am trying to summarise is from Call Centre loads, and specifically the AHT ( Average Handle Time), ACD Time Average, and the ACW average, and the Column with the formula has the Average Hold time, which is supposed to be AHT minus ACD average minus ACW average. All this is call centre data.

The exclusion of the AM and PM display is a management request.
My personal concern in this role is that the figures work out.
And on top of that I am meant to implement the requests of management.
If managements requests are unachievable. then I give my opinion to that effect and say "how would you like the information displayed?"
Outside of that, if Management below the Upper level change my figures and reports, then I cannot be held responsible for that. Especially if the reports become unusable.

However since all I am trying to do is display my figures from the raw data as time displays without AM or PM, then I'll do what I can.

Thanks for your help

Ta