Re: QDE (Quick Date Entry)

From: hgrove (hgrove.1byyu8_at_excelforum-nospam.com)
Date: 09/02/04 

Date: Thu, 2 Sep 2004 13:52:58 -0500

Frank Kabel wrote...
..
>Nice procedure BTW BUT you mentioned the restrixtion: The
>user has to enter in YYYY-MM-DD format. This is not very
>common for most userss (though it would solve several issues).
>Most of our code is not the parsing but dealing with different
>formats

The user DOES NOT have to enter dates in yyyy-mm-dd format. The macro
does. The user would enter short dates as 1-, 2-, 3-, 4-, 6- or 8-digit
numbers, and the macro would rearrange the digits and insert dashes to
form ISO yyyy-mm-dd date strings and enter then into cells with General
number format. Since such ISO dates are always interpretted as dates
when entered into cells with General number format, this seems to solve
the *macro* entry and formatting issue in one pass.

I'd already guessed that most of the QDE code is dealing with different
formats and the GUI interface for user options overhead, and I'll repeat
(with emphasis) that MOST INDIVIDUAL USERS NEED *ONE* *AND* *ONLY* *ONE*
DATE FORMAT most of the time, and only occasionally need to switch to
alternative 2- or 3-digit short date entry treatment. Most users will
*NEVER* switch between MDY, DMY or YMD entry order.

QDE is the logical opposite of a one-size-fits-all approach. It's an
all-sizes-included-even-though-you-need-only-one approach. It's like
asking for a hammer and being handed a 20kg toolbox that includes a
hammer and having to carry around the toolbox as you use the hammer.

>Now you only have to define what 4 digits represent :-)
>YYMD
>MMDD

Something QDE doesn't handle either. The variations are (for MDY entry
order)

MDYY
MDDY
MMDY
MMDD

QDE chokes on 7799, which should be 7-July-1999, even when a single QDE
entry cell is selected. I'll admit my macro doesn't handle such entries
either, but I hadn't meant it to do so. If I had, I'd have used a
defined name to store my ambiguous entry resolution preferences as an
array of text like

{"MMDD","MDDY","MMDY","MDYY"}

then used them to convert 4-digit entries into ISO yyyy-mm-dd date
strings and used the first one that resolved to a valid date.

Anyway, it should have been clear from my macro's code that anything
with 4 or fewer digits is treated as month and day without year. I
bypass 5 digit entries because they could be date serial numbers. I
suppose I could have added 7 digit entries, but I just didn't see the
point.

>And yes you're correct. The Format function works as expected
>in other languages as well
..

So unless you're working around Gregorian vs Hijri, entering ISO
yyyy-mm-dd date strings into cells formatted General will result in a
date serial number using the system's default date format.

Speaking of Gregorian vs Hijri, I take it QDE won't be much if any use
for much of the Muslim world?

>FWIW I would not use Sendkeys in any product/etc. I just
>wouldn't rely on it :-)

Why not? Bad experience using it?

Now that I've dug deeper, QDE also makes Registry entries under

HKEY_CURRENT_USER\Software\QDE

but using to Tools > Add-ins to unload it doesn't eliminate these
Registry entries, so QDE leaves cruft in the Registry when uninstalled.
Where's the mentioned in the documentation? 

---
Message posted from http://www.ExcelForum.com/