Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mimic Time Mask (WIN2000-XL97)

    I am relatively new at Excel - and I do know there is no "input mask" similar to Access.

    What I would like to do, is format a cell to only accept a time in the following format: 06:45 or 15:25

    Now if I format the cell using toolbar Format/Cells/Category:Time,Type 13:00, the cell will be formatted for that, but the user can type 1145 and it will not work properly.

    How can I format it, but also force the user to enter it the way I want him/her to?

    Thanks for your help!

    Michael

  2. #2
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mimic Time Mask (WIN2000-XL97)

    Go to Data / Validation , i think you will ifnd your answer there

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mimic Time Mask (WIN2000-XL97)

    Thank you so much.
    It is EXACTLY what I want. And a bonus messagebox too? WOW <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I appreciate your help !

    Michael

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mimic Time Mask (WIN2000-XL97)

    Well, 1 glitch, and it may just be me.

    As validation, I selected
    Time
    between
    06:00
    and
    17:00

    It works as advertised until I try to enter 06:99. Excel turns it into 0.31875

    I do have the column formatted as Time/13:00 (per Format/Cells)

    What am I still missing?

    Sorry to be a pest - but I am so close.... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Michael

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Mimic Time Mask (WIN2000-XL97)

    I don't think 6:99 is a time, is it?

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  6. #6
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mimic Time Mask (WIN2000-XL97)

    Everyone's a comedian!! <img src=/S/clown.gif border=0 alt=clown width=15 height=15>
    OK, ya got me!! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I'm just trying to force the user to conform.
    I set the format as Time/13:00
    I set the validation to accept only times between 06:00 and 17:00

    I was hoping validaton would check for a 'valid' time between 06:00 and 17:00

    Maybe I am looking at this wrong then.

    I was so happy that this worked - even having a messagebox pop up to tell the
    user to change 1600 to 16:00 etc.

    But how do I check for 06:99?

    Or can I ?

    Thank you again !
    Michael

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Mimic Time Mask (WIN2000-XL97)

    You probably will have to do something other than DAta Validation using a macro.
    By using DAta VAlidation and TIME, excel will accept ANY number Between (in your example 6AM and 5PM) 0.25 (=6/24) and 0.70833 (=17/24) no matter how entered. Excel is "NOT" anal about time entries and will convert if "convertible" to a valid time.

    6:99 is converted to 6 hours and 99 minutes = (6 +99/60)/24 = 0.31875 which is BETWEEN 0.25 and 0.70833 so IS a valid time. If you format the cell to time, you can see it is = 7:39 AM, which IS a valid time AND within your range.

    I do NOT think you can trap this in ANY way with validation, since you have to trap the ENTERED values BEFORE excel converts them to a number. You would have to use some other entry system (some kind of INPUT box) OR just accept that some "entered times" will be valid if excel can make a time of them.

    Steve

  8. #8
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mimic Time Mask (WIN2000-XL97)

    Thank you for the detailed explanation. I'm an Access man at heart, and was hoping
    to create an Excel input mask of some kind.
    I will stick with the Format Cell/Validation method for now and just hope ( ! ) the
    users cooperate !!

    Thanks again everyone!
    Michael

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mimic Time Mask (WIN2000-XL97)

    You have found what I think is a bug in Excel. It looks like 6:99 is accepted as a time by data valadation, and by the input routine which converts it to 0.31875 which is 7:39 which is 6 hours and 99 minutes. If you apply the Time format after this is entered, it will display as 07:39. The bug is either that it is accepted as a time, or that it is stored with the "General" format when it is put in the cell and not the Time format even if the time format was applied to the cell before the value was entered.

    The only way around this would be to write your own data validation routine in VBA using the worksheet change event routine.
    Legare Coleman

  10. #10
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mimic Time Mask (WIN2000-XL97)

    Thanks for that Legare - I think I am going to go the 'simple' route for now. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    But I do understand what you're saying - I gotta code it if I want what I want ! <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

    Thanks again !
    Michael

  11. #11
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mimic Time Mask (WIN2000-XL97)


Posting Permissions

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