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

Topic

New Reply

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

Reply | Quote

Replies

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:

=IsADate(A1)

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:

=B2

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

=Personal.xls!IsADate(A2)

Reply | Quote

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

Reply | Quote

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).

Reply | Quote

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

Reply | Quote

The value is accepted and converted to 02/08/1942

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.

Reply | Quote

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?

TIA

Reply | Quote

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.

Reply | Quote

Apologies – you’re right…

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.

Reply | Quote

> 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”.

Reply | Quote

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

Reply | Quote