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]

The Three column formula Works, I just had to format the the Cells to time, as in the Custom mm:ss, and the result calculates the right numbers if entered right( Full format time).

The key thing is that I have been asked to format a Sheet with no AM or PM display.
I have been told it's possible, but not how to do it.


Ta.


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

Sorry, tried also everything but you cannot enter minute and second only . It always convert your data to hours & minutes & seconds.

I could not also understand your formula because the first formula should give me 26 seconds
(10:37-6.04-3.07) and not 45 minutes and 23 seconds.

 

[santeria]

It did not strike me until after the 50 minute edit time had elapsed, but using the formula, 10:37-6:04-3:07 Gives a result 1:26 ( Minutes and seconds).
The 45 Min, and 23 seconds is what I think I got in previous text and standard time formats.
If worked using a Macro, the time format may give , for example, :02, instead of 00:02 if entered manually.
The problem is that even with a mm:ss format, the keying in of the data goes ( for the above formula) 0:10:37 - 0:06:04 - 0:03:07
In immediate terms I am not too concerned about this, although I wish I could put in 10:37 and have it come in as 10 minutes and 37 seconds.

My Main concern is how to change the time display so it does not display with the AM or PM, or even the A/P option, but to remove the AM and PM display altogether.

Ta.



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

I could not also understand your formula because the first formula should give me 26 seconds
(10:37-6.04-3.07) and not 45 minutes and 23 seconds.[/quote][/quote]

 

[santeria]

Okay, cpearson's page seems to be showing the way:

Question: If you want to change a format in the Entire file of a Worksheet, what is the Macro header needed to change the display, and what key factors are needed in a redefinition of the Worksheet format display.... i.e. the Removal of AM or PM under certain Circumstances.

Ta

 

[santeria]

If anyone knows of a Guide to editing a format to the Worksheet_change macro... I would appreciate it... still working on changing the AM and PM display; i'm finding bits and pieces, but nothing as yet that would get that pesky AM and PM display out of the picture.

Pearson's macro seems to be thge closest, but I guess I am doing something wrong

Or if Pearson is not the place to find the appropriate macro, then I would appreciate a reference to an appropriate site.


Ta

Okay, cpearson's page seems to be showing the way:

Question: If you want to change a format in the Entire file of a Worksheet, what is the Macro header needed to change the display, and what key factors are needed in a redefinition of the Worksheet format display.... i.e. the Removal of AM or PM under certain Circumstances.

Ta

 

[Chitosunday]

Now it has become clearer, if the right answer is 1:26 then what we can do is format your three cells as text and right align it. Then for the answer, format it as [h]:mm

 

[Chitosunday]

Another short cut is format all your four cell as [h]:mm then input your data and formula

 

[Chitosunday]

Just notice that my second option has an am pm. Use my first option of using text format for the data and for the formula as
[h]:mm, it will produce what you want .

 

[santeria]

The main four cells are just part of it.
These are the principle example.
There are some other time formatted raw data summaries on the first file.
The summary line gives a daily summary to a third file, and this third file is populated by data from Daily reports from Sept 29 going October 24th, later this week.

If anyone can help with a formula or VBA that removes the AM and PM, I would appreciate it; unfortunately, just changing format to a non-time format will not work.
Ultimately, if I can't Change the AM/PM display from within the file, then the display will have to be artificially displayed as text or whatever.

I have already tried the other formats, and only time format works, since it is working with time displays of minutes and seconds.

Ta



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

Just notice that my second option has an am pm. Use my first option of using text format for the data and for the formula as
[h]:mm, it will produce what you want .

 

[santeria]

Thus far, I found a Macro on cpearson's site.
It seemed to be the most likely candidate to change the AM/PM default format,I'll post that below, with minor changes I made.
The aim is to get the Time cells to format up as 2 digit minute with 2 digit second display.
It is unlikely that the majority of cells with time format's will exceed 4-6 figures, especially since the calls are meant to be within the 3-7 minute range, 30-40 or so tops.
The only difference is that certain time cells appear as numerical second displays... i.e. 745 second or 947 seconds, but this is in raw data sheet and is usually easily changed by division for time format.

*****TIME CHANGE MACRO : BROAD EXAMPLE

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Range("A65536")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If

Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01
TimeStr = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12
TimeStr = "00:" & .Value
Case 3 ' e.g., 735 = 7:35
TimeStr = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
TimeStr = Left(.Value, 1) & ":" & _
Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
TimeStr = Left(.Value, 2) & ":" & _
Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub

 

[Chitosunday]

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.