+ Reply to Thread
Results 1 to 2 of 2

Data verify problem

  1. 12-01-2008, 10:46 PM #1
    G-fer
    G-fer is offline
    Registered User
    Join Date
    08-19-2006
    Posts
    6

    Unhappy Data verify problem

    Hi All ...

    I have a sheet where I need to enter the amount of time taken to do a task in the form hh:mm. The cell of interest, B5, is formatted "Custom", "hh:mm" and data validation is set as "Time", "00:00:00 to 23:59:59".

    Seems to be Ok ... enter 4:35 and it works. Enter 4,35 and validation traps the error. However, accidently enter 4:65 (instead of 4:35) and the cells format wierdly changes to "General", and the figure changes to 0.218806. If you right click on the cell and change the format back to "Custom" "hh:mm", the content changes to 05:05.

    How can I trap an entry where the minutes part of the data is accidently greater than 59?

    Regards ... G-fer.
    Reply With QuoteRegister To Reply
  2. 12-01-2008, 11:07 PM #2
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    If you change the cell format to [h]:mm or [hh]:mm, it will not change if minutes > 60 are entered.

    To me, 4:65 seems like a legit input; Excel's willingness to accept it validates that, IMO.

    To avoid accepting such inputs would require, I think, a text input with conversion to time in another cell, or a forms textbox (where you can do input validation in code). Both overkill, methinks.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1