QDE (Quick Date Entry)

Hi Community of Excel Users!

This is a joint posting from 4 of the "regulars" to these newsgroups:
Bob Phillips, Frank Kabel, Ron de Bruin, and Norman Harker.

QDE.xla Version 1 is now available *free* from:

Bob Phillips
http://www.xldynamic.com/source/xld.QDEDownload.html
and
Ron de Bruin
http://www.rondebruin.nl/qde.htm

A long outstanding feature request in these newsgroups has been for a
robust method by which dates can be entered as numbers only. By
"robust" we mean a method that will stand up to the fullest possible
range of Operating System settings.

Although this is Version 1, it has been tested as much as possible
within the constraints we faced. However, we believe that it is now
ready for release and we hope that the community of Excel users will
appreciate what we have tried to achieve and will have no problems
with using it.

It comes with the usual full money back if not delighted guarantee;
"You pay nothing for it and you'll get nothing back!"

Edited thrilling highlights of what QDE is all about are provided
below our sign-off with further details in the Help File and at the
link above.

Regards


Norman Harker, Sydney, Australia
Bob Phillips, Poole, England
Frank Kabel, Frankfurt, Germany
Ron de Bruin, Netherlands


***** Edited Highlights of QDE

*** Background:
This is a joint effort of 4 of the "regulars" Bob Phillips, Frank
Kabel, Ron de Bruin and Norman Harker.

*** Why QDE?
Entry of dates into Excel is not too difficult to master for any given
set of Operating System Regional Settings although users have to
appreciate the inbuilt assumptions that Excel makes regarding various
date entry forms.

However, where large numbers of dates need to be entered, Excel's date
serial number approach imposes obligations to use date separators (eg.
03-05-2004).

Where many dates are required to be entered, this imposes a
requirement for more key presses than might be achieved. For example,
we might prefer just to enter
0305 and have this interpreted "correctly" as 03-05-04. If we can do
this, we save 4 key presses. Big deal? Not really! Unless, that is,
you have many hundreds or even thousands of such dates to enter.

This is why we need QDE. We need a means by which we can economize on
the number of keystrokes required to enter dates.

*** What Is QDE?
QDE is an Excel Addin that provides quick input of dates, in all
international formats. With QDE you now have the option to enter dates
using numbers only.
These are interpreted by QDE as dates based upon settings which you
will rarely, if ever, need to change.

QDE is a stand-alone utility, that once installed is available to all
of your workbooks and worksheets. It does not change anything on the
worksheet until directed to by you the user.
Once dates are entered they are treated by Excel as dates entered
using non-QDE methods. Workbooks constructed with the assistance of
QDE do not require that QDE should remain installed or even be present
on whatever computer the workbook is open on.

*** Where Did It Come From?
Date entry has been a perennial problem in Excel. Many people want to
input the date components (day. month, year) without having to bother
with the date delimiters. The problem here is that Excel then
interprets this as a normal number, not a date. Chip Pearson made the
first serious attempt at a solution that we know of, which you can see
at Chip's site on his Date and Time Entry page, Whilst it does show
some of the issues that need to be addressed, it is US date centric,
and there are a couple of problems with the input.
Norman Harker decided that a full international version of this
routine was needed. Norman is an Excel MVP based in Sydney, Australia
and is a regular contributor to the Excel newsgroups. He has a special
interest in Excel's date handling capabilities. Norman posted a
tentative solution which was hitting problems.

There were many follow up responses to this query, mainly from Frank
Kabel, based in Frankfurt, Germany and Bob Phillips, based in Poole,
England. Both are regular contributors to the Excel newsgroups. The
discussions moved the problem on somewhat, but as usual, further
issues were revealed as the "regulars"
educated themselves on the complexities involved.

At this point, Norman, Frank and Bob decided informally that the
newsgroups were not the best forum for development work and informally
agreed to set up a team to tackle the problem. Ron de Bruin, another
Excel MVP, was recruited to provide the extra skills and experience
needed, and QDE was conceived.
There were many problems along the way to what we have here. These
were mainly those of ensuring that all international issues, operating
system settings and Excel versions were covered. This is the result of
that development, and we hope that you enjoy it and find it useful.

If you are interested, you can see the original newsgroup discussion
that gave rise to QDE at this Google thread

http://tinyurl.com/3luwe

*** How It Works
This is a classic case of, "Easier said than done!"

Excel has limited date interpretation capabilities that are inherent
in Excel not using a dedicated data type (as is common for database
programs).
Excel uses the date serial number concept whereby dates are nothing
more than a format of a number with 1 representing a base date.
However, users, want to enter dates quickly with as few numbers and
key presses as is possible, and to have Excel interpret those numbers
as the dates that the user intends.

The trouble is that there are three interacting issues:

1. There are two date serial number bases in Excel; the 1900 Date
System where 1 represents 1-Jan-1900 and the 1904 Date System where 1
represents 1-Jan-1904.
The 1900 Date System is the default setting for Windows and the 1904
Date System is the default setting for Mac. But Excel for Windows also
allows you to change the default setting to the 1904 Date System.

2. Ordering the three parameters of day month and year varies
according to the user's country custom. In the US (predominantly)
entry is Month Day Year. In some Asian countries and in countries
that have adopted the International Standard ISO8601:2000 the order is
Year Month Day. The rest of the world (when using the Gregorian
Calendar), including most of Europe, UK, South America and former
British Colonies all use Day Month Year.

3. The quick date entry of a user might be from 3 to 8 digits long
depending upon whether or not it was a single or double digit day or
month number and whether or not they were entering a double digit or
four digit year (or, indeed, wanted the current year to be assumed).
QDE handles quick date entry interpretation and reflects these three
interacting issues. In most situations those issues are handled only
once for a given range or worksheet and most often the user will not
even have to change the parameters that QDE takes from current Excel
and Operating System settings.

End of Post.

very many thanks. I shall download the addin. meanwhile my gratitude to
you guys for making our lives easier. thanks again;.




Norman Harker wrote in message
...
Hi Community of Excel Users!

This is a joint posting from 4 of the "regulars" to these newsgroups:
Bob Phillips, Frank Kabel, Ron de Bruin, and Norman Harker.

QDE.xla Version 1 is now available *free* from:

Bob Phillips
http://www.xldynamic.com/source/xld.QDEDownload.html
and
Ron de Bruin
http://www.rondebruin.nl/qde.htm

A long outstanding feature request in these newsgroups has been for a
robust method by which dates can be entered as numbers only. By
"robust" we mean a method that will stand up to the fullest possible
range of Operating System settings.

Although this is Version 1, it has been tested as much as possible
within the constraints we faced. However, we believe that it is now
ready for release and we hope that the community of Excel users will
appreciate what we have tried to achieve and will have no problems
with using it.

It comes with the usual full money back if not delighted guarantee;
"You pay nothing for it and you'll get nothing back!"

Edited thrilling highlights of what QDE is all about are provided
below our sign-off with further details in the Help File and at the
link above.

Regards


Norman Harker, Sydney, Australia
Bob Phillips, Poole, England
Frank Kabel, Frankfurt, Germany
Ron de Bruin, Netherlands


***** Edited Highlights of QDE

*** Background:
This is a joint effort of 4 of the "regulars" Bob Phillips, Frank
Kabel, Ron de Bruin and Norman Harker.

*** Why QDE?
Entry of dates into Excel is not too difficult to master for any given
set of Operating System Regional Settings although users have to
appreciate the inbuilt assumptions that Excel makes regarding various
date entry forms.

However, where large numbers of dates need to be entered, Excel's date
serial number approach imposes obligations to use date separators (eg.
03-05-2004).

Where many dates are required to be entered, this imposes a
requirement for more key presses than might be achieved. For example,
we might prefer just to enter
0305 and have this interpreted "correctly" as 03-05-04. If we can do
this, we save 4 key presses. Big deal? Not really! Unless, that is,
you have many hundreds or even thousands of such dates to enter.

This is why we need QDE. We need a means by which we can economize on
the number of keystrokes required to enter dates.

*** What Is QDE?
QDE is an Excel Addin that provides quick input of dates, in all
international formats. With QDE you now have the option to enter dates
using numbers only.
These are interpreted by QDE as dates based upon settings which you
will rarely, if ever, need to change.

QDE is a stand-alone utility, that once installed is available to all
of your workbooks and worksheets. It does not change anything on the
worksheet until directed to by you the user.
Once dates are entered they are treated by Excel as dates entered
using non-QDE methods. Workbooks constructed with the assistance of
QDE do not require that QDE should remain installed or even be present
on whatever computer the workbook is open on.

*** Where Did It Come From?
Date entry has been a perennial problem in Excel. Many people want to
input the date components (day. month, year) without having to bother
with the date delimiters. The problem here is that Excel then
interprets this as a normal number, not a date. Chip Pearson made the
first serious attempt at a solution that we know of, which you can see
at Chip's site on his Date and Time Entry page, Whilst it does show
some of the issues that need to be addressed, it is US date centric,
and there are a couple of problems with the input.
Norman Harker decided that a full international version of this
routine was needed. Norman is an Excel MVP based in Sydney, Australia
and is a regular contributor to the Excel newsgroups. He has a special
interest in Excel's date handling capabilities. Norman posted a
tentative solution which was hitting problems.

There were many follow up responses to this query, mainly from Frank
Kabel, based in Frankfurt, Germany and Bob Phillips, based in Poole,
England. Both are regular contributors to the Excel newsgroups. The
discussions moved the problem on somewhat, but as usual, further
issues were revealed as the "regulars"
educated themselves on the complexities involved.

At this point, Norman, Frank and Bob decided informally that the
newsgroups were not the best forum for development work and informally
agreed to set up a team to tackle the problem. Ron de Bruin, another
Excel MVP, was recruited to provide the extra skills and experience
needed, and QDE was conceived.
There were many problems along the way to what we have here. These
were mainly those of ensuring that all international issues, operating
system settings and Excel versions were covered. This is the result of
that development, and we hope that you enjoy it and find it useful.

If you are interested, you can see the original newsgroup discussion
that gave rise to QDE at this Google thread

http://tinyurl.com/3luwe

*** How It Works
This is a classic case of, "Easier said than done!"

Excel has limited date interpretation capabilities that are inherent
in Excel not using a dedicated data type (as is common for database
programs).
Excel uses the date serial number concept whereby dates are nothing
more than a format of a number with 1 representing a base date.
However, users, want to enter dates quickly with as few numbers and
key presses as is possible, and to have Excel interpret those numbers
as the dates that the user intends.

The trouble is that there are three interacting issues:

1. There are two date serial number bases in Excel; the 1900 Date
System where 1 represents 1-Jan-1900 and the 1904 Date System where 1
represents 1-Jan-1904.
The 1900 Date System is the default setting for Windows and the 1904
Date System is the default setting for Mac. But Excel for Windows also
allows you to change the default setting to the 1904 Date System.

2. Ordering the three parameters of day month and year varies
according to the user's country custom. In the US (predominantly)
entry is Month Day Year. In some Asian countries and in countries
that have adopted the International Standard ISO8601:2000 the order is
Year Month Day. The rest of the world (when using the Gregorian
Calendar), including most of Europe, UK, South America and former
British Colonies all use Day Month Year.

3. The quick date entry of a user might be from 3 to 8 digits long
depending upon whether or not it was a single or double digit day or
month number and whether or not they were entering a double digit or
four digit year (or, indeed, wanted the current year to be assumed).
QDE handles quick date entry interpretation and reflects these three
interacting issues. In most situations those issues are handled only
once for a given range or worksheet and most often the user will not
even have to change the parameters that QDE takes from current Excel
and Operating System settings.

End of Post.

Now XL2K SP-3.

I'll admit I'm torture testing now.

I select B2:C7 as QDE entry range in an activated worksheet. I select that
entire range, type 1 and press [Ctrl]+[Enter]. All cells contain 1. I have
to select individual cells to get them formatted as dates, but this doesn't
trigger invalid date entry. Also, every cell in this range displays
12/31/1899 when it's the ActiveCell, so there's definitely a bug in the
SelectionChange event handler - even in XL2K.

However, single cell entry in multiple cell selections does work.

Harlan Grove wrote:
Now XL2K SP-3.

I'll admit I'm torture testing now.
vbg
yes you're are but that's totally o.k.


I select B2:C7 as QDE entry range in an activated worksheet. I select
that entire range, type 1 and press [Ctrl]+[Enter]. All cells contain
1. I have to select individual cells to get them formatted as dates,
but this doesn't trigger invalid date entry. Also, every cell in this
range displays 12/31/1899 when it's the ActiveCell, so there's
definitely a bug in the SelectionChange event handler - even in XL2K.

I tracked this annoying display error down and this is again Excel's
one-day of error for dates prior to 01-March-1900. The VBA function
Format does interprete '1' as 31-Dec-1899. So VBA is again handling
some values differently than the worksheet function. (e.g. try
?Format(1,"MM/DD/YYYY") in the intermediate window.
Put this on our bug list to create a workaround for this behaviour.

We will also consider multi-cell entries (with CTRL+ENTER) as a
possible enhancement for the next version


However, single cell entry in multiple cell selections does work.
Relieved :-))

Frank