display in edit mode hh:mm:ss.00 as entered

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I get Excel to display **in edit mode** hh:mm:ss.00 data the way I
entered it?

I've entered data as hh:mm:ss.00 and formatted the cell the same way and it
displays AOK. BUT if I enter edit mode - say via F2 to make an entry
correction - the hundredths of seconds aren't displayed AND AM/PM is
displayed plus it displays it as since 12 AM not as my data is from a nominal
zero point!!

Eg I enter 00:26:57.80. The cell is formatted hh:mm:ss.00 and displays as
entered. BUT in edit mode it appears as "12:26:58 AM". I can't edit the
hundredths plus I need to reorient my brain to cope with the 12: and AM/PM ;-(

How can I get Excel to display in edit mode hh:mm:ss.00 data the way I
entered it?

TIA
b1
 
I couldn't find a way to do it (yech!).

Maybe you could use a helper cell...

Just copy|paste to that other cell and it'll remind you what you used to have
there.

(double yech!)

I'm betting that you only have a certain range in a certain worksheet that has
this trouble.

Try this against a copy of your workbook--or close without saving!

Rightclick on the worksheet tab that has this behavior.
select view code
paste this into the code window:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myFormat As String
Dim myStr As String

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("b:b")) Is Nothing Then Exit Sub

myFormat = "##:##:##.##"

On Error GoTo errHandler:

If Target.Text Like myFormat Then
myStr = InputBox(prompt:="What's the new time?", Default:=Target.Text)
Application.EnableEvents = False
If myStr Like myFormat Then
'ok to put back
Target.Value = CDbl(TimeValue(Left(myStr, 8)) _
+ Application.Round((Right(myStr, 2) / 24 / 60 / 60 / 100), 15))
End If
End If

errHandler:
Application.EnableEvents = True

End Sub

Change this line to match your range of time cells:
If Intersect(Target, Me.Range("b:b")) Is Nothing Then Exit Sub

(I use column B)

Then back to excel and select a in column B that has a time with that format in
it.

(watta pain!)
 
Thanks Dave - glad/sad to know its not just me! I'll try and give the macro a
run but might end up just living with it ... ie re-enter if I find an entry
error. Calculations seem fine and if I cut & past the data its transferred as
hh:mm:ss.00 so the original entry isn't "lost".
 
Back
Top