Share via

How do I limit a user's entry to a specific time format (hh:mm)?

Anonymous
2016-12-29T22:05:17+00:00

PROJECT:

I've created a sheet to assist care providers with calculating their weekly time spent with recipients without going over the recipient's allotted "Maximum Weekly Hours".  The sheet allows the user to enter their hours and minutes worked per day and then it totals their hours for the week, compares it to their Maximum Weekly Hours allotted and tells the user how many hours/minutes they are over or under the allotted amount.  

PROBLEM:

The problem comes when a user isn't paying close attention and they enter "4" instead of "4:00" or "04:00" which causes the sheet to return the value of "96:00" instead of "4:00".  

PRESUMED SOLUTION:

Create a Data Validation entry that will only allow an entry using the hh:mm format (ie. '4:00' instead of '4').  

INCORRECT FIX:

I've tried Data Validation with these entries: Settings tab, Allow=Custom, Ignore Blank=checked, Data=dimmed out, Formula=[h]:mm.   When I enter " 4 " in the cell it gives me the warning "Need to use hh;mm blah, blah".  But when I enter " 4:00 " or " 04:00 " I get the same error.  For some reason it's not seeing the hh:mm format I've used as being correct.  

I have a feeling I'm using the Data Validation function wrong or the entry I've put in the "Formula" box isn't right.  Any thoughts?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2016-12-31T21:05:09+00:00

    Yes, the data validation solution would allow you to customize your pop up. Just adjust the 'Error Message' to whatever you want it to say upon invalid data entry. The 'Input Message' is what you would see when you select the cell with validation in it. Usually this is used to direct the user into what kind of data should be entered.

    I completely understand the need for getting data right the first time. Data Validation is what you need. Don't hesitate to post back if you need more information or help.

    Happy New Year's!

    1 person found this answer helpful.
    0 comments

Answer accepted by question author

  1. Anonymous
    2016-12-29T22:42:13+00:00

    Hi Michael,

    There are several workarounds for this. The easiest way for this to happen is if you use data validation with 'Time' as the 'Allow' method. Then just set your time to between 0:00 and 23:59.

    Another way is to have an intermediary cell calculate the value of the input for you. Let's assume you have three cells here:

    • Time Input
    • Time Format
    • Time Calculation

    Your 'Time Input' cell can be the same cell you're using, where users are entering "4:00" or "4". The 'Time Format' cell can have an intermediary calculation in it looking at the 'Time Input' and adjusting for user error. This assumes the user will not be entering in time values greater than 24 hours, or one day. Here is an example of the formula you could use for your 'Time Format' cell:

        =IF(TimeInput>1,TimeInput/24,TimeInput)

    Another way you could write it is:

        =TimeInput/IF(TimeInput>1,24,1)

    Both give the same result.

    Then, the 'Time Calculation' is your downstream calculation, where instead of pointing it to the original 'Time Input' cell, you'd point it to the 'Time Format' intermediary calculated cell.

    And another way altogether is to use a UserForm with VBA. This is a bit more in-depth, but gives you much more manual control of user inputs and what to do with them.

    In any case, I'd recommend the first (data validation) solution.

    Regards,

    Zack Barresse

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-12-31T08:40:54+00:00

    Hi Zack - 

    Thanks for getting back to me on this question.   You obviously know your way around a spreadsheet and your time is very much appreciated.  

    I've included a screen capture of the timesheet I'm working on.  The provider enters their time in the orange boxes under "Weekly Hours to Report", adjacent to "Day 1", "Day 2", etc.  This is where they should ONLY enter their time of, for example, 2 hours and 15 minutes of work using the format 02:15 (as opposed to entering "2.25" for 2 and 1/4 hour or entering "135" for minutes).  I need the sheet to pop up a warning for 2.25 or 135 instructing them to reformat their entry to 02:15.  Would your solutions cause this type of pop-up?

    [A brief background to show the need for Data Validation - This sheet is simply used by the Provider to get their figures worked out and 'correct' before they have to physically write their time on the pre-printed, computer-scanned, government form that is mailed out to be completed and returned for processing.  Because it's read by a computer they do not allow any type of mistake, cross-out, over-write, or other corrections or markings on the form of any kind.  To make one can cause delays of days or weeks before the Provider gets paid.  So it's important that this 'reference tool' be correct before the Provider's fill out their form and any type of error, such as entering a '2' and having it calculate incorrectly as '48:00' be prevented.]

    0 comments