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]

As i think I wrote previously, text is not an option for the purposes of calculating values, but if I had a working formula which was hidden, and then a reference to the summary page in text, especially text which had no AM or PM, but had the 1:03 style of format, then that would "look" the way it is wanted to look; however, the goal is to get the values working and usable without the AM and PM display.
If the Custom format had a way of deleting the AM/PM display, then that would work.

But thus far it looks like what I have been asked to do is basically not feasible; however, I had understood that it was possible, it just required a detailed VBA code to over-ride standard display.

Ta

********************

Cpearson macro will just change what you input in cell a65536 from number to time format with am/pm. Hence, not applicable. The only solution to remove am/pm is really a text format for your data and formatting the formula cell to
[hh]:mm or [h]:mm which will compute a generic result which is the same as minute and second . The only problem here is that
if the second is 3 digits as the formula will only give correct answer for only two digit format.

 

[Chitosunday]

If you used [h]:mm in your format, you can calculate even if the format is text as excel convert it to number when using + or - but not the function command.

 

[santeria]

Thanks for your reply.
I had tried the text " solution" before; but since it provides numbers for display purpose only, and is not an actual solution to present time without an AM/PM designation.
I really need either a format way in the cell dialogue box, or a Macro way to manipulate the time display, in such a way as the change travels with the file.

Ta



*******************

If you used [h]:mm in your format, you can calculate even if the format is text as excel convert it to number when using + or - but not the function command.

 

[Chitosunday]

The text format for data will be fine. For the formula you can use this just enclose it with "" like =mmss("your formula").
Press alt f11 if there is no directory at the right press ctrl r
then right click the directory of thisworkbook insert module then put this code in the module:

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 cvfinb < 0 Then
Do
cvfinb = cvfinb + 60
cvfina = cvfina - 1
Loop Until cvfinb >= 0
End If
If cvfinb > 60 Then
cvfina = cvfina + Int(cvfinb / 60)
cvfinb = cvfinb Mod 60
End If
cel = Empty
oper = emtpy
tem = Empty
cva = emtpy
cvb = Empty
mmss = cvfina & ":" & WorksheetFunction.Rept(0, 3 - Len(Str(cvfinb))) & cvfinb
cvfina = Empty
cvfinb = 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
End Sub

 

[santeria]

Will this affect the time formatted cells universally ?
Does it operate as soon as the File is reopened or does it have to be specially run ?

Ta

 

[Chitosunday]

It will operate as soon as the file is open just enable macro.
The mmss function will operate only on text format time.

 

[Chitosunday]

Give your email address and i will send you a sample file

 

[santeria]

I hope you got my email addresses.
Anyway, I'm at work now, so I can test run the Macro to see if that does the job.

I just had my Flu shot this morning, and since I have this horrible reaction to needles, I am not too focused... so I may end up emailing my reports to the local Daycare centre if I don't have this caffeine get my heart started

Anyway, much appreciated, looking forward to that email file.

Ta



************************

Give your email address and i will send you a sample file

 

[santeria]

I tested the Macro and it fails at one line.
The error is " Run Time Error '1004':
Method 'Range' of object'_Global' Failed "

The error on the debugger give line:

cval = Range(cel).Value

which is 8 lines down on the last sub, as the problem

I don't know what happened.

Ta



***************

 

[Chitosunday]

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