Entering 24 Hour Clock without entering :

[JB Bates]

I am trying to figure out how to enter military time into a spreadsheet and
not have to manually enter the colon. I am using Vista as an operating
system and Excel 2007

EX enter 1330
it appears in cell as 13:30

In a previous post I saw a response that said to do the following:

"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"

I tried this and it does not work - don't know if it is a Vista issue. Any
help is GREATLY appreciated!!

Jennifer

 

[Jim Thomlinson]

Check out this link...

http://www.cpearson.com/excel/DateTimeEntry.htm

 

[JB Bates]

AWASOME!!!! This worked!! thank you for directing me to that article. I
also changed my date fields to be entered without "/"

You have totally made my week!! thanks!

 

[JB Bates]

Is it possible to have this apply to more than one range in a spreadsheet?

I substituted x5:ac7 instead of the a1:a10 range

now i want to apply this ALSO to ranges I11:L20 AND O11:R20

THANKS

 

[Jim Thomlinson]

change your range to...

If Application.Intersect(Target, Range("x5:ac7, I11:L20, O11:R20")) Is
Nothing

 

[Gord Dibben]

Range("X5:AC7, I11:L20, O11:R20")


Gord Dibben MS Excel MVP