Entering HH:MM

[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

 

[Don Guillett]

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

 

[Guest]

PERFECT!!

Thank you so much, Don!!

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

 

[Don Guillett]

Glad to help

 

[JB Bates]

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?

 

[Gord Dibben]

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

 

[JB Bates]

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

 

[Gord Dibben]

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

 

[JB Bates]

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

 

[Gord Dibben]

Works for me.

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


Gord