Entering HH:MM

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

Guest

Excel 2003

I have a co-worker who enters time on a spreadsheet. She would like to have
the time cells fomatted so when she enters 1456 it appears in the cell as
14:56. This doesn't work when I format, cell, time, 13:30. I've looked at
format, cell, special (which would be a logical place to put something like
this), to no avail.

TIA
CaroleO
 
Preformat the column as time and try this.
Right click sheet tab>view code>insert this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 5 Then Exit Sub
Application.EnableEvents = False
Target = Left(Target, 2) & ":" & Right(Target, 2)
Application.EnableEvents = True
End Sub
 
PERFECT!!

Thank you so much, Don!!

CaroleO
Don Guillett said:
Preformat the column as time and try this.
Right click sheet tab>view code>insert this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 5 Then Exit Sub
Application.EnableEvents = False
Target = Left(Target, 2) & ":" & Right(Target, 2)
Application.EnableEvents = True
End Sub
Click to expand...
 
Hi Don

I am trying to do this same thing in windows Vista and when I do what you
directed it does not work. If I enter 1300 in the cell it displays as 0000
and the formula lines shows "7/23/1903 12:00:00 AM" any thoughts?
 
Did you place Don's code in the sheet module as directed?

Are you entering 1300 in a cell in column E? Any other column will not
trigger the event.


Gord Dibben MS Excel MVP
 
Yes I did. and i even tried opening a new sheet with nothing in it and going
through all these steps. still get the dame results
 
You mention Windows Vista.

That is an operating system, which has nothing to do with Excel.

What version of Excel or Office?

I will assume 2007.

Did you save the workbook as a macro-enabled workbook(*.xlsm)?

Have you enabled macros when the workbook opens?


Gord
 
I thought being VISTA might be an important fact, sorry

Yes Excel 2007
Yes saved as XLSM
Yes Macros enabled.

Still does not work.

Is there another way to have it read with other than the code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 5 Then Exit Sub
Application.EnableEvents = False
Target = Left(Target, 2) & ":" & Right(Target, 2)
Application.EnableEvents = True
End Sub
 
Works for me.

I see you started another thread elsewhere and received some satisfactory
replies.


Gord
 
Back
Top