Time formatting
Hi
Does anyone have a method for entering any number, say, 0800 and it will automatically insert a colon in between the 08 and 00. I have a method of automatically changing data entered in lower case to upper case, but cannot work this one out - Can you? -- Kind Regards Mick |
Time formatting
See
http://cpearson.com/excel/DateTimeEntry.htm In article , "Mick Southam" wrote: Does anyone have a method for entering any number, say, 0800 and it will automatically insert a colon in between the 08 and 00. I have a method of automatically changing data entered in lower case to upper case, but cannot work this one out - Can you? |
Time formatting
Hi,
Put this in your Worksheet Code Module: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Application.Intersect(Range("B2:B100"), Target) _ Is Nothing Then GoTo e: With Target If .Cells.Count 1 Then GoTo e: If Not IsNumeric(.Value) Then GoTo e: If .HasFormula Then GoTo e: If .Value Like "[!0123456789]" Then GoTo e: Select Case Len(.Value) Case 1: .Value = "00:0" & .Value Case 2: .Value = "00:" & .Value Case 3, 4: .Value = Left(.Value, 1) & ":" & Right(.Value, 2) Case Else: .ClearContents End Select If .Value = 1 Then .ClearContents .NumberFormatLocal = "HH:MM" End With e: Application.EnableEvents = True End Sub -- Regards, Soo Cheon Jheong _ _ ^вп^ -- |
All times are GMT +1. The time now is 08:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com