Removing AM and PM from time displays in a WorkSheet

  • Thread starter Thread starter Campbell
  • Start date Start date
C

Campbell

Is it possible , using formula format ( Custom), or ( in
the alternative using a Worksheet_Change macro) to make
sure that AM or PM does not display in an Excel File.
I realise that changing the regional settings will allow
for the AM and PM to be removed, but I am after a setting
that will display in the file, and will over-ride any
settings that the person viewing the file will have.
Basically, I have Minute summaries ( usually either a
basic single or double digit minute, with a Second range)
that need to remain as time format, but the manager I am
doing the report for wants the AM and PM to not be
displayed so that a VP in the company will be "happy" with
the format.

All responses greatly appreciated.
 
Campbell,

Format - Cells - Number - Date or Time. Or Custom, and roll your own. Leave
out the AMPM part.
 
Thanks for the reply.
Actually, been there done that.
Every format I use has an AM or PM display.
All I need to do is display time format. I have
experimented with a Visual Basic solution, or Macro
solution would do the job... but I need to have the data
in the cells in time format... Mainly because the Minute
is 60 seconds, and the decimal value is 100 equals 1... I
only mention that because some people have suggested that
I Format the Results in text, after calulating separately,
well, apparently some VP gets to see the report, and he
wants to work the figures, but has supposedly asked for
the display to be without the AM or PM.


I am kind of stuck because the system seems set for AM or
PM display in time values.... the Numbers are all for a
Call centre place, with ACD,ACW, and the IVR basic figures
in what I am doing.... would work better in Crystal
Reports I know, but since I was hired on the basis of
Excel :-)... Well, who knew some manager was going to
request that I remove the AM and PM display...

UNLESS.... am I missing some format change in the File ?
I need a change that will stay with the file.
I am trying to massage a Macro I picked from a web site,
but since I am still on a learning curve with VBA, I don't
know entirely what to do.

Ta.


***********************
 
Campbell,

Why don't you give an example? Tell us exactly what appears in the cell.
Second, do Format - Cells - Number - Custom, and give us the exact format
code in the cell. Third, do Edit - Clear - Formats (undo when finished) to
determine the exact unformatted contents of the cell, and give us that.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Campbell said:
Thanks for the reply.
Actually, been there done that.
Every format I use has an AM or PM display.
All I need to do is display time format. I have
experimented with a Visual Basic solution, or Macro
solution would do the job... but I need to have the data
in the cells in time format... Mainly because the Minute
is 60 seconds, and the decimal value is 100 equals 1... I
only mention that because some people have suggested that
I Format the Results in text, after calulating separately,
well, apparently some VP gets to see the report, and he
wants to work the figures, but has supposedly asked for
the display to be without the AM or PM.


I am kind of stuck because the system seems set for AM or
PM display in time values.... the Numbers are all for a
Call centre place, with ACD,ACW, and the IVR basic figures
in what I am doing.... would work better in Crystal
Reports I know, but since I was hired on the basis of
Excel :-)... Well, who knew some manager was going to
request that I remove the AM and PM display...

UNLESS.... am I missing some format change in the File ?
I need a change that will stay with the file.
I am trying to massage a Macro I picked from a web site,
but since I am still on a learning curve with VBA, I don't
know entirely what to do.

Ta.


***********************
Click to expand...
 
Okay.
First example: in the principle columns that seem to give
me grief:
F24 G24 H24 I24
10:24 05:49 02:59 01:36

FORMAT:
mm:ss mm:ss mm:ss mm:ss

NO-FORMATS:
F24 G24 H24 I24
0.007222222 0.004039352 0.002071759 0.001111111


There are some other columns which give some grief like
this, but the major one is a duplicate for another Skill
Set, and another on the same tab is just a time format
issue which is note for note the above issue.

The Cells work out the calculations seemingly fine, and
worked even better after I macroed them, the only hassle
was that a manager wanted principle values from the wrong
point in the data.
The Manager I am doing the AM/PM attempt for is requesting
this, based on a VPs request I believe.
Anyway, Hopefully the above will stay as I see it
formatted now.

Ta







-----Original Message-----
Campbell,

Why don't you give an example? Tell us exactly what appears in the cell.
Second, do Format - Cells - Number - Custom, and give us the exact format
code in the cell. Third, do Edit - Clear - Formats (undo when finished) to
determine the exact unformatted contents of the cell, and give us that.
Click to expand...
 
Campbell,

You asked that it not display AM or PM, and your example agrees; it's in
fact not showing AM/PM. So I don't think I understand what you want.
 
Okay,

My imprecise/or misleading statement, totally accidental.
What I mean is that, the Formula bar displays AM and PM
for all the values, in fact it displays a full day value,
which I personally do not care about... HOWEVER, I have
been asked to get a solution to where the AM and PM do not
display in the formula bar, just the time value.

So far, every person I have talked with has said that the
solution is to display the results as text.
Superficially this will work, but the thing is that the VP
who wants the AM and PM gone from the Formula Bar, will
take about less than 2 seconds to realise that the formula
and details are not what they seem in the text format....
hence my so far fruitless search for a Macro or piece of
VB that will stop the display of the AM and PM values in
the formula bar.

Ta.
 
Use HH:mm:ss as time format in your regional windows settings
(start>settings>control panel>regional settings)
HH means 24 hour I believe while hh is 12 hour, so sneak into the VPs
office and change his/her settings <g>
 
Campbell,

The VP wants the AM PM removed from the formula bar??? Sheesh! I mean, who
the devil cares? Find him/her a good therapist. :) The formula bar is a
diagnostic tool. You can format the sheet itself for virtually any
appearance needed -- that's what's important.

Maybe Peo's solution will work. Or maybe you can remove the formula bar
entirely (View - Formula bar).

Good luck!

Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------
 
I told the Manager who wants the AM/PM gone so that the VP
will be happy, that AM/PM displays in all formats realting
to time.
I went over the regional thing with the Manager. I
explained that the system just works that way.
I never said, but personally, all I really care is that
the data is right, the formulas work, and any Macro I can
do to speed things up works correctly.

So far, the only consistent comment I have got is the text
based workaround. The key formula is =mmss("D9-d10-d11")
in the total cell. At the core of this is a Macro written
by someone else, that somehow facilitates the text to
time event...
***************
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

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

And as near as I understand it, this is made up of Two
Macros.

Well, it suffers from the problem that as soon as someone
works with the cell's, then the illusion goes.

I had been told, or rather it was suggested to me, that
Custom format would allow me to minus out the AM or PM
option.

Anyway, it's totally loopy. But, the manager said it is
what she wants because it's what the VP wants.

Well, they have sent me around and around on a simple
spreadsheet till I am just teaching myself VB info.
The text option will display the info in the right way
without the time format. I can Macro that easily enough.

It's just that this stupid AM/PM thing is not going to
work except by an illusion, which someone has given to me
in the above Macro, but if I follow that way, it will
affect my two raw data files for the entire system I
loaded reports from.

As it is now, I can do reports on just about any aspect of
the call loads in two different skill sets, two separate
tiers, and using Two principle raw data sets, and two
principle service data files....

But they want the AM/PM off the display... and no, I
can;t take it off the Settings :-)
Thats an idea... but the VP and the manager are just smart
enough to be stupid....
I explained to the Manager that it can't be done without
re-writing the core code , and all she said was " Oh...
you're killing me"...


Sorry people... but I had to try; I hope you understand.
Maybe there is a way, especially since I just need a
display of time.

Stupid thing about it is that if I had a Mac, I could do
it no problems.

Ta
 
Back
Top