Solved: Time format validation

[georgiboy]

Hi Experts

I am looking for a worksheet routine to validate cells formatted as "hh:mm". The cells are currently formatted this way and i would like them to stay this way.

Heres my problem i have two columns containing times, lets just say A and B for example. I would like so...

1) if someone was to type "0600" then it will format it to "06:00" and keep the cell format.

2) if someone typed "06.00" it would become "06:00" or "06;00" would become "06:00" and so on.

3) if they type "06:00" to leave it alone.

All the times are in 24 hour format so i need it to work with all times.

Hope this is enough info please let me know if you need more

Cheers

George

[Bob Phillips]

06.00 will be tough, because the cell will be in a time format, so it will show as 0, even converting it back to General will lose the decimal point.

[georgiboy]

So Bob you think this is a no go then.

I would be happy without the 06.00 because they mostly use 06;00 for some weird reason, they lack the ability to hold the shift key as well as push another button to enter the time correctly.

[Bob Phillips]

Changing 0600 to 6:00 is simple. So would 6;00. The problem arises if they have a vaid time in the cell and then enter say 6.00. I can't see how you can catch that as 6.00, the value is 6, the view of it is 00:00.

[georgiboy]

If you run this then change the cell format to "hh:mm" manually then it will change to the correct format. But if you change the format within the code it does revert to 6.

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)

'If Len(Target.Value) = 3 Then
'Target.NumberFormat = "General"
'Target.Value = "0" & Left(Target.Value, 1) & ":" & Right(Target.Value, 2)
'Target.NumberFormat = "hh:mm"
'End If

'If Len(Target.Value) = 4 Then
'Target.NumberFormat = "General"
'Target.Value = Left(Target.Value, 2) & ":" & Right(Target.Value, 2)
'Target.NumberFormat = "hh:mm"
'End If

Target.NumberFormat = "@"
If Mid(Target.Value, 3, 1) = "." Then
Target.Value = Left(Target.Value, 2) & ":" & Right(Target.Value, 2)
'Target.NumberFormat = "hh:mm"
End If

End Sub[/VBA]

Strange

[georgiboy]

Ok i have done it using a worksheet selection change and a worksheet change together here it is for info, feel free to tell me how i can do it better

[vba]Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A:B")) Is Nothing Then

If Len(Target.Value) = 4 Then
Target.Value = Left(Target.Value, 2) & ":" & Right(Target.Value, 2)
Target.NumberFormat = "hh:mm"
End If

If Len(Target.Value) = 5 Then
If Mid(Target.Value, 3, 1) = "." Then
Target.Value = Left(Target.Value, 2) & ":" & Right(Target.Value, 2)
Target.Value = TimeValue(Target.Value)
Target.Value = "0" & Left(Target.Value, 4)
Target.NumberFormat = "hh:mm"
Else
If Mid(Target.Value, 3, 1) <> "." Then
If Mid(Target.Value, 3, 1) <> ":" Then
Target.Value = Left(Target.Value, 2) & ":" & Right(Target.Value, 2)
Target.NumberFormat = "hh:mm"
End If
End If
End If
End If

End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Target.NumberFormat = "@"

End Sub[/vba]
Hope this info helps others

[georgiboy]

Ok Bob and all nearly there thought i had it but not quite, how can i make this only apply to columns A and B?

[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Value = "" Then
Target.NumberFormat = "@"
End If

End Sub[/VBA]

Thanks in advance

George

[georgiboy]

Ok sorry to mess you all about but i have done it

Here it is for info...

[vba]Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A:B")) Is Nothing Then

On Error GoTo End1

If Len(Target.Value) = 4 Then
Target.Value = Left(Target.Value, 2) & ":" & Right(Target.Value, 2)
Target.NumberFormat = "hh:mm"
End If

If Len(Target.Value) = 5 Then
If Mid(Target.Value, 3, 1) = "." Then
Target.Value = Left(Target.Value, 2) & ":" & Right(Target.Value, 2)
Target.Value = TimeValue(Target.Value)
Target.Value = "0" & Left(Target.Value, 4)
Target.NumberFormat = "hh:mm"
Else
If Mid(Target.Value, 3, 1) <> "." Then
If Mid(Target.Value, 3, 1) <> ":" Then
Target.Value = Left(Target.Value, 2) & ":" & Right(Target.Value, 2)
Target.NumberFormat = "hh:mm"
End If
End If
End If
End If

End If
End1:

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo End1
If Not Intersect(Target, Range("A:B")) Is Nothing Then
If Target.Value = "" Then
Target.NumberFormat = "@"
End If
End If

End1:
End Sub[/vba]
Hope this helps someone

[Bob Phillips]

I didn't play with it much, but it didn't seem to do much for me, and what it did do suffered the sort of problems I mentioned.

600 - nothing happened
6:00 - changed to 6::00 (sic!)
1500 - changed to 15:00, but left justified
1500 in the cell just done, change to 0::25

[GTO]

Greetings George,

Just poked my head in for a moment, but saw this. Have you considered this alternative? http://vbaexpress.com/kb/getarticle.php?kb_id=75 by: STEINER

Admittedly, it does not fix ";" or "." - but offers a different take.

I have found it "easier" to get users (who are used to MIL time) to enter times the way they say them ("Oh-Six-Thirty," "Zero-Seven-Fifteen," etc..), ie - w/o the colon, rather than trying to correct whatever gooberish symbols they want to throw in (as you succinctly pointed out).

Anyways, not trying to throw ya a "curve ball", just another thought...

Mark

PS: I would note the one "issue" I recollect experiencing is when user misses a key. For instance: attempts to enter 1230 hrs, and misses trailing zero. Solution converts to 0123 hrs...

[georgiboy]

@ GTO Thanks i will have a look at that, did you try my solution as it seems to work for me but not for Bob?

@ Bob i take it you are using the worksheet change and the selection change part (just making sure), i use excel 2007 this wont make a difference will it?

Granted though it does come out left justified. And you are right overtyping times causes a problem, i need to investigate further.

Having tested it further it does do everything Bob says it does, i need to make it more idiot proof.

Thanks GTO & Bob more work is needed

[georgiboy]

Thank you both for your input, just to let you know i have given up!!!

@ GTO i have made use of your link and i am going to use Data validation to only allow text strings between 3 and 4 and the code in the link provided, it should at least prevent most anomolies. I will breif my team to only enter military time "not that they will listen"

Many thanks

George

[GTO]

Hey George,

I never thought of combining Data Validation with the code, sounds good! Presuming you meant to only allow "between 3 and 4" in length, allowing only 4 in length would eliminate where the user enters "123" and it's converted to 01:23 (see my PS at #10).

Anyways, just an idea, and if I see something better, will tack on or PM you.

Mark

[georgiboy]

Just for info, i now use this.