Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date validation and formatting (WinXP / O2003 SP2 - UK)

    Hi,

    Have an Excel sheet where users provide data.
    One field is "report date" - dates should be in formats "dd-mm-yyyy" or "dd-mmm-yyyy"

    Currently users can type in any sort of number - needless to say it results in some pretty odd dates... ;o)

    What is in your opinion the best way to "restrict" user input...? (change event, validation cell...)


    TIA
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date validation and formatting (WinXP / O2003 SP2 - UK)

    Put the following function in a standard module:

    Public Function IsADate(ByVal Arg) As Boolean
    On Error GoTo ExitHere
    IsADate = IsDate(Arg) And Arg > 0
    ExitHere:
    End Function

    Say the user enters dates in A2:A100
    In B2 entere the following formula:
    <code>
    =IsADate(A1)
    </code>
    and fill down to B100.
    Select A2:A100.
    Select Data | Validation.
    Select Custom from the Allow dropdown list.
    Enter the following formula in the box:
    <code>
    =B2
    </code>
    Activate the Error Alert tab.
    Make sure the style is set to Stop,
    Enter an appropriate error message.
    Click OK.

    Notes:
    - You can hide column B now.
    - You can use another column instead of B.
    - If you put the function in your Personal.xls, use
    <code>
    =Personal.xls!IsADate(A2)</code>

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date validation and formatting (WinXP / O2003 SP2 - UK)

    Thanks Hans,

    Not sure that this really does the trick.
    I'm still allowed to type in for instance 15380 (someday in 1941)

    What I need is something that also enforces an input mask - similar to what you have in for instance MS Access.

    Not sure to what extend this really is possible in Excel? (that is, without having to use some thirdparty add-ins...)


    TIA
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date validation and formatting (WinXP / O2003 SP2 - UK)

    I don't understand that. When I try this, I get an error message if I try to enter 15380.

    Excel does not really have input masks (and I am not in favor of input masks for dates).

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

    Re: Date validation and formatting (WinXP / O2003 SP2 - UK)

    What happens if the cell is formatted as a date before you enter 15380?
    Legare Coleman

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date validation and formatting (WinXP / O2003 SP2 - UK)

    The value is accepted and converted to 02/08/1942 <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    I don't know if there is an ideal solution for this. At work we have an application that is used by people in many countries, with date formats such as mm/dd/yyyy, dd/mm/yyyy, dd-mm-yyyy, dd.mm.yyyy and yyyy-mm-dd. An input mask can't handle all these formats. Using separate boxes for day, month and year was deemed unacceptable. So in the end, the designer decided to let users enter dates the way they want, and to let Windows decide whether it's a valid date according to the local settings. The display format is fixed, with a label explaining the display format, so that there can be no confusion. The rest is up to the users.

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date validation and formatting (WinXP / O2003 SP2 - UK)

    Hi Hans,

    Apologies for late response, but I've been out of "office". / ;o)

    Attached is a workbook in which I have followed your description to the point.
    This is the third PC that I've made an attempt on - so far with the same result on all three of them...
    As the sample works on your system... - could it be that something has been forgotten in your initial description? <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    TIA
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date validation and formatting (WinXP / O2003 SP2 - UK)

    1) Calculation has been set to Manual in your workbook, so the formulas in column B will not be updated automatically when the user enters or edits a value in column A.

    2) You haven't set Data | Validation correctly. The validation formula for cell A2 is =B65505 but it should be =B2.

  9. #9
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date validation and formatting (WinXP / O2003 SP2 - UK)

    Apologies - you're right... <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22>

    The reason why it didn't work as intended in my other tests, were that the input cell were formatted as "date" (as Legare asked).
    Input here is deemed to be acceptable and are simply converted in to a date. So it seems to me that the format of the input cell is rather important.

    When I changed it to "General" the test sheets worked fine.
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date validation and formatting (WinXP / O2003

    > On Error GoTo ExitHere
    Presumably solely to trap the potential for a #VALUE error?
    I'm asking because I'm developing a customised date-checker function, e.g. "date must be valid and month must NOT be June".

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date validation and formatting (WinXP / O2003

    Yes, it's to ensure that the function returns TRUE or FALSE in all circumstances, even if the argument is an error value.

Posting Permissions

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