Solved: Time format validation

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

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.

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.

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.

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

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 :banghead:

[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 :igiveup:

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

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

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

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

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

@ 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 :)

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

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

Just for info, i now use this.