N
Norman Harker
Hi Harlan!
In line comments. But I'll start as I finish by thanking you for the
effort put in to giving us high quality feedback.
I was *not* thinking about that problem at all. There was *never* any
intention to address that issue, which came up much later. I don't
think that the introduction or any other material indicates anything
different. [The medical researchers' problem came from Excel
interpreting some of the genetic strings (such as DEC12) as dates when
they "should" have been treated as string text. Solution to that
problem was not one intended for QDE. I think that you and I agreed
that the real problem was in Research 1.01 <vbg> "Ensure validity of
data before you analyse!" was the "expletives deleted" comment I made
to myself when I looked at the issue and cut out all the crap in a
learned journal article]
The research I refer to is research from base records (such as hand
written medical records, completed forms, historic records or from
questionairres) where large numbers of dates are being inserted in
Excel.
Maybe, a later version might add a facility to interpret 12DEC type
entries as dates but it goes outside the brief of interpreting plain
numerics as dates. I wouldn't be recommending it now but (see later).
For now? Perhaps an addition to Help saying what it does *not* do or
what it does *not* accept or translate. I'll also suggest that we add
that it doesn't read your mind or make coffee. That comes later.
Turning off Intellisense. You're talking revolution here! A different
project but a useful one because we all get a little miffed every time
it cuts in and gets it wrong. Microsoft regards it as a "feature" but
then they regard Clippy as a feature too. Maybe that will be
introduced in the next version of Excel. No harm in living in hope
whilst remaining in the real World.
One issues with dates that we keep coming back to is that base problem
of Excel not having a separate data type for dates. Whilst waiting for
Nirvana, it might be possible to develop a QDE type approach to
declare a date entry range that allows entry of a fully (Aaaargh!)
comprehensive range of date forms inclusive of numerics, ordinals, and
existing forms. This would certainly hit the same (and worse)
international issues. I'm under no illusions as to the difficulties
involved as I have done some work on the differences between returns
dependent upon dmy or mdy Regional settings although I haven't
extended it to ymd except for isolating the only unambiguous forms of
entry that are allowed). It wouldn't be easy but the QDE exercise has
revealed a lot of the issues. We've made life difficult for ourselves
by starting off with the principle that we are not just covering the
problems of one Regional setting or even one language.
For some time Baroness Thatcher, as she is now called, was often
refered to (even / especially within her own party) as "Tina". Many
called her different names with the most famous being "the Iron lady".
Edward Heath, who she took over the leadership from, would never
mention her by name and always referred to her as, "*That* woman".
taken a very narrow view of what researchers do. From that viewpoint,
you *are* correct. But in the university system I see many cases where
researchers are entering dates from thousands of questionaires. I'll
admit though that many of their problems would not arise if their
research techniques were better. The crucial pilot survey should be
used to address data entry problems.
But we also see cases where the researcher has no control over the
base data; old manually kept records are still in frequent use. UK for
example has one of the best continuous health record systems in
existence because of the structure of the health care system they
introduced in 1948. (Each person had their "own" General Practitioner
who kept a record which passed on to any new GP). 50 odd years of
continuous records to analyse (eg) to establish links between
childhood diseases and... And with a "free" system that set of records
is not corrupted by use or non-use of GPs. Those records were manual
and are a fantastic research resource.
That's just one example. There are many other cases of researchers
using manually recorded base records often from different sources with
different modes of representing dates. There has to be a faster way of
getting those dates into Excel if that is the (rightly or wrongly)
chosen program.
A very good analogy of what happens if you don't ensure that what you
get is what you want before you proceed. That is addressed in Help for
QDE which recommends that step before you get too far.
batch of pre- entered data applying the same principles that are used
for individually entered dates.
There will be a lot of issues here. I can see some users of that type
of facility using the combined power of the add-in and their computer
to corrupt their data at high speed and not realise it. It would
require "health warnings" but some of those issues might be addressed
by interposing a, "This is what you'll get" for a carefully selected
list of dates. Plus perhaps an undo. Perhaps even a programatic back
up of the file. We can try and make it "idiot proof" but making it
"bloody idiot proof" is where we have to call for those genetic
researchers to give us alternatives to the baseball bat or brick.
really is appreciated. In a face to face situation would at least
warrant a few beers.
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
In line comments. But I'll start as I finish by thanking you for the
effort put in to giving us high quality feedback.
I was *not* thinking about that problem at all. There was *never* any
intention to address that issue, which came up much later. I don't
think that the introduction or any other material indicates anything
different. [The medical researchers' problem came from Excel
interpreting some of the genetic strings (such as DEC12) as dates when
they "should" have been treated as string text. Solution to that
problem was not one intended for QDE. I think that you and I agreed
that the real problem was in Research 1.01 <vbg> "Ensure validity of
data before you analyse!" was the "expletives deleted" comment I made
to myself when I looked at the issue and cut out all the crap in a
learned journal article]
The research I refer to is research from base records (such as hand
written medical records, completed forms, historic records or from
questionairres) where large numbers of dates are being inserted in
Excel.
Maybe, a later version might add a facility to interpret 12DEC type
entries as dates but it goes outside the brief of interpreting plain
numerics as dates. I wouldn't be recommending it now but (see later).
For now? Perhaps an addition to Help saying what it does *not* do or
what it does *not* accept or translate. I'll also suggest that we add
that it doesn't read your mind or make coffee. That comes later.
Turning off Intellisense. You're talking revolution here! A different
project but a useful one because we all get a little miffed every time
it cuts in and gets it wrong. Microsoft regards it as a "feature" but
then they regard Clippy as a feature too. Maybe that will be
introduced in the next version of Excel. No harm in living in hope
whilst remaining in the real World.
One issues with dates that we keep coming back to is that base problem
of Excel not having a separate data type for dates. Whilst waiting for
Nirvana, it might be possible to develop a QDE type approach to
declare a date entry range that allows entry of a fully (Aaaargh!)
comprehensive range of date forms inclusive of numerics, ordinals, and
existing forms. This would certainly hit the same (and worse)
international issues. I'm under no illusions as to the difficulties
involved as I have done some work on the differences between returns
dependent upon dmy or mdy Regional settings although I haven't
extended it to ymd except for isolating the only unambiguous forms of
entry that are allowed). It wouldn't be easy but the QDE exercise has
revealed a lot of the issues. We've made life difficult for ourselves
by starting off with the principle that we are not just covering the
problems of one Regional setting or even one language.
For some time Baroness Thatcher, as she is now called, was often
refered to (even / especially within her own party) as "Tina". Many
called her different names with the most famous being "the Iron lady".
Edward Heath, who she took over the leadership from, would never
mention her by name and always referred to her as, "*That* woman".
I disagree! We don't and can't do everything for researchers. You'veNewsgroup ettiquette prohibits reference to other names said:
taken a very narrow view of what researchers do. From that viewpoint,
you *are* correct. But in the university system I see many cases where
researchers are entering dates from thousands of questionaires. I'll
admit though that many of their problems would not arise if their
research techniques were better. The crucial pilot survey should be
used to address data entry problems.
But we also see cases where the researcher has no control over the
base data; old manually kept records are still in frequent use. UK for
example has one of the best continuous health record systems in
existence because of the structure of the health care system they
introduced in 1948. (Each person had their "own" General Practitioner
who kept a record which passed on to any new GP). 50 odd years of
continuous records to analyse (eg) to establish links between
childhood diseases and... And with a "free" system that set of records
is not corrupted by use or non-use of GPs. Those records were manual
and are a fantastic research resource.
That's just one example. There are many other cases of researchers
using manually recorded base records often from different sources with
different modes of representing dates. There has to be a faster way of
getting those dates into Excel if that is the (rightly or wrongly)
chosen program.
A very good analogy of what happens if you don't ensure that what you
get is what you want before you proceed. That is addressed in Help for
QDE which recommends that step before you get too far.
I agree that it might be a useful additional facility to convert a
batch of pre- entered data applying the same principles that are used
for individually entered dates.
There will be a lot of issues here. I can see some users of that type
of facility using the combined power of the add-in and their computer
to corrupt their data at high speed and not realise it. It would
require "health warnings" but some of those issues might be addressed
by interposing a, "This is what you'll get" for a carefully selected
list of dates. Plus perhaps an undo. Perhaps even a programatic back
up of the file. We can try and make it "idiot proof" but making it
"bloody idiot proof" is where we have to call for those genetic
researchers to give us alternatives to the baseball bat or brick.
Thanks very much for taking the time to give us vigorous feedback. It
really is appreciated. In a face to face situation would at least
warrant a few beers.
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
)