Entering 24 Hour Clock without entering :
 

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 tabview codeinsert 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

Entering 24 Hour Clock without entering :
 

Check out this link...

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

Jim Thomlinson


"JB Bates" wrote:

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 tabview codeinsert 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

Entering 24 Hour Clock without entering :
 

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!

"Jim Thomlinson" wrote:

Check out this link...

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

Jim Thomlinson


"JB Bates" wrote:

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 tabview codeinsert 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

Entering 24 Hour Clock without entering :
 

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

Check out this link...

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

Jim Thomlinson


"JB Bates" wrote:

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 tabview codeinsert 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

Entering 24 Hour Clock without entering :
 

change your range to...

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

Jim Thomlinson


"JB Bates" wrote:

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

Check out this link...

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

Jim Thomlinson


"JB Bates" wrote:

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 tabview codeinsert 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

Entering 24 Hour Clock without entering :
 

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


Gord Dibben MS Excel MVP

On Mon, 5 Oct 2009 12:47:01 -0700, JB Bates
wrote:

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

Check out this link...

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

Jim Thomlinson


"JB Bates" wrote:

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 tabview codeinsert 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