Consulting

Results 1 to 14 of 14

Thread: Solved: Time format validation

  1. #1
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    571
    Location

    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
    I was not told it was impossible, so i did it.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,149
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    571
    Location
    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.
    I was not told it was impossible, so i did it.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,149
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    571
    Location
    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
    I was not told it was impossible, so i did it.

  6. #6
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    571
    Location
    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
    Last edited by georgiboy; 12-02-2008 at 12:35 PM.
    I was not told it was impossible, so i did it.

  7. #7
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    571
    Location
    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
    I was not told it was impossible, so i did it.

  8. #8
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    571
    Location
    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
    I was not told it was impossible, so i did it.

  9. #9
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,149
    Location
    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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  10. #10
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,362
    Location
    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...

  11. #11
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    571
    Location
    @ 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
    I was not told it was impossible, so i did it.

  12. #12
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    571
    Location
    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
    I was not told it was impossible, so i did it.

  13. #13
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,362
    Location
    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

  14. #14
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    571
    Location
    Just for info, i now use this.
    I was not told it was impossible, so i did it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •