When we go to archives to record genealogical and historical
information, I usa a databade program,. askSam, but my wife uses a
spreadsheet, because she is an accountant, and that isd what she is
familiar with.

But when we try to print the information out for filing, Excel has
"helpfully" changed it.

For example, one archival reference was MOOC 13/1/3119

Excel printed this out as 13/01/19, which would be a quite different
file in the archives filing system, and so useless as a reference.

I have notice this behaviour elsewhere, when I was using a spreadsheet
to index books, because I wound it easier than setting up a database
program to do it.

Even if one defined a column as "Text" in Excel, it still interpreted
some entries as dates, and changed them.

Eventually I used Libre Office Calc, which did not seem to have that
particular problem.

Spreadsheets can sometimes be useful for simple "flat file" database
projects, but the erratic behaviour of Excel can be a pitfall for the
unwary.



--
Steve Hayes from Tshwane, South Africa
Web: http://www.khanya.org.za/stevesig.htm
Blog: http://khanya.wordpress.com
E-mail - see web page, or parse: shayes at dunelm full stop org full stop uk

This is dependent on how you output to file. If you use Excel's
built-in methods then expect these kinds of issues. Using VBA standard
file I/O methods is a much more efficient way to write worksheet data
to a file, or file data to a worksheet.

While Libre Calc doesn't have some of Excel's irritating nuances, it
does have a few of its own. It's still, though, the best 'free'
spreadsheet program available. (WPS might qualify as a good runnerup
for 2nd place)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On 05/07/15 15:22, Steve Hayes wrote:
When we go to archives to record genealogical and historical
information, I usa a databade program,. askSam, but my wife uses a
spreadsheet, because she is an accountant, and that isd what she is
familiar with.

IT departments often encounter this situation, especially with accountants.

Write out 100 times "A spreadsheet is not a database"

But in this particular case were MOOC and 13/1/3119 in different
columns? If not this seems a particularly egregious error - even if
making assumptions about date-like strings is acceptable a spreadsheet
really shouldn't be trying to parse sub-strings to look for dates.

--
Hotmail is my spam bin. Real address is ianng
at austonley org uk

On 05/07/15 15:22, Steve Hayes wrote:
When we go to archives to record genealogical and historical
information, I usa a databade program,. askSam, but my wife uses a
spreadsheet, because she is an accountant, and that isd what she is
familiar with.

IT departments often encounter this situation, especially with
accountants.

Write out 100 times "A spreadsheet is not a database"

But in this particular case were MOOC and 13/1/3119 in different
columns? If not this seems a particularly egregious error - even if
making assumptions about date-like strings is acceptable a
spreadsheet really shouldn't be trying to parse sub-strings to look
for dates.

Not trying to be combative...
When I enter 13/1/3119 or 1/13/3119 in a cell, format doesn't change
(the cell is formatted 'General').

A spreadsheet is nothing more than a glorified grid control. Grid
controls are what users typically use to work with data stored in a
database file. It doesn't really matter what the filetype is, but
matters more *how the data is handled* between the file and the grid
control.

Thus, the key to manipulating your data in a specific grid control lies
in *knowing how to use the grid control effectively for the task at
hand*!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Sun, 05 Jul 2015 19:12:39 +0100, Ian Goddard
wrote:

On 05/07/15 15:22, Steve Hayes wrote:
When we go to archives to record genealogical and historical
information, I usa a databade program,. askSam, but my wife uses a
spreadsheet, because she is an accountant, and that isd what she is
familiar with.

IT departments often encounter this situation, especially with accountants.

Write out 100 times "A spreadsheet is not a database"

But in this particular case were MOOC and 13/1/3119 in different
columns? If not this seems a particularly egregious error - even if
making assumptions about date-like strings is acceptable a spreadsheet
really shouldn't be trying to parse sub-strings to look for dates.

If you look it up in the online index, MOOC and 13/1/3119 are in
separate fields.

In this example:

DEPOT KAB
SOURCE MOOC
TYPE LEER
VOLUME_NO 13/1/2121
SYSTEM 01
REFERENCE 46
PART 1
DESCRIPTION HOFFMAN, ELIZABETH MOUNT. LIQUIDATION AND
DISTRIBUTION ACCOUNT.
STARTING 19130000
ENDING 19130000

Excel turned the Volume No into a date -- 13/01/21

I'm not sure that Microsoft Access is much better, however. I once
tried to make a fairly simple database with names, addresses and dates
of birth, and the first person I entered had a birthdate of 1 Jan
1926, which MS Access changed to 1 Jan 2026, even though I typed in
the full date. I wasted a couple of days trying to get it to wrok,
then went back to using askSam, but my wife prefers to use
spreadsheets, because that is what she is familiar with.

Next time we go to the archives I'll try to persuade her to use
OneNote.




--
Steve Hayes from Tshwane, South Africa
Web: http://www.khanya.org.za/stevesig.htm
Blog: http://khanya.wordpress.com
E-mail - see web page, or parse: shayes at dunelm full stop org full stop uk

On Sun, 05 Jul 2015 14:37:54 -0400, GS wrote:

On 05/07/15 15:22, Steve Hayes wrote:
When we go to archives to record genealogical and historical
information, I usa a databade program,. askSam, but my wife uses a
spreadsheet, because she is an accountant, and that isd what she is
familiar with.

IT departments often encounter this situation, especially with
accountants.

Write out 100 times "A spreadsheet is not a database"

But in this particular case were MOOC and 13/1/3119 in different
columns? If not this seems a particularly egregious error - even if
making assumptions about date-like strings is acceptable a
spreadsheet really shouldn't be trying to parse sub-strings to look
for dates.

Not trying to be combative...
When I enter 13/1/3119 or 1/13/3119 in a cell, format doesn't change
(the cell is formatted 'General').

But did you try to print it?

When I said the records were wrong, my wife checked, and the data was
as she entered it. It was in printing that the entry was changed.


--
Steve Hayes from Tshwane, South Africa
Web: http://www.khanya.org.za/stevesig.htm
Blog: http://khanya.wordpress.com
E-mail - see web page, or parse: shayes at dunelm full stop org full stop uk

But did you try to print it?

When I said the records were wrong, my wife checked, and the data was
as she entered it. It was in printing that the entry was changed.

It prints exactly as displayed whether I open the file in Excel 9, 10,
11, 12, or 14 on either of my XP Pro SP3, Win7 Pro, or Win8.1 machines!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Like I already stated...

*it matters more how the data is handled between the database file and
the grid control*

...meaning the method used to import the data to the worksheet.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Sun, 05 Jul 2015 20:39:36 -0400, GS wrote:

But did you try to print it?

When I said the records were wrong, my wife checked, and the data was
as she entered it. It was in printing that the entry was changed.

It prints exactly as displayed whether I open the file in Excel 9, 10,
11, 12, or 14 on either of my XP Pro SP3, Win7 Pro, or Win8.1 machines!

Well then the version on my wife's computer can't be any of those,
because hers *did* behave like that.

For what it's worth, it came with MS Office 10.


--
Steve Hayes from Tshwane, South Africa
Web: http://www.khanya.org.za/stevesig.htm
Blog: http://khanya.wordpress.com
E-mail - see web page, or parse: shayes at dunelm full stop org full stop uk

On Sun, 05 Jul 2015 20:39:36 -0400, GS wrote:

But did you try to print it?

When I said the records were wrong, my wife checked, and the data
was as she entered it. It was in printing that the entry was
changed.

It prints exactly as displayed whether I open the file in Excel 9,
10, 11, 12, or 14 on either of my XP Pro SP3, Win7 Pro, or Win8.1
machines!

Well then the version on my wife's computer can't be any of those,
because hers *did* behave like that.

For what it's worth, it came with MS Office 10.

Do you mean MS Office 2010, or MS Office 2002 (v10)? Regardless, I
tested with all versions from v9 to v14 across the 3 OSs I mentioned.
Are you sure she doesn't have it set up to only display 2-digit years?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Sun, 05 Jul 2015 16:22:34 +0200, Steve Hayes
wrote in soc.genealogy.computing:

For example, one archival reference was MOOC 13/1/3119

Excel printed this out as 13/01/19, which would be a quite different
file in the archives filing system, and so useless as a reference.

I was using formerly Excel on my main computer and OpenOffice
on the laptop, now LibreOffice on both.

OO and LO accepted for years to enter pre-1900 dates as dates
while Excel considered them as texts. I found a method to
handle that similarly, by prepending a ' to the cell.

So enter '13/1/3119 to keep it as is. I enter all complete dates
like that, even after 1900. In a few cases, the quote is still
shown, when the date is wrong, i.. '12-31-2000 will be right
but '13-31-2000 will show the quote.


Denis

--
Denis Beauregard - généalogiste émérite (FQSG)
Les Français d'Amérique du Nord - www.francogene.com/genealogie--quebec/
French in North America before 1722 - www.francogene.com/quebec--genealogy/
Sur cédérom Ã* 1785 - On CD-ROM to 1785

For clarity...

Office9 is MSO2000
Office10 is MSO2002
Office11 is MSO2003
Office12 is MSO2007
Office14 is MSO2010
....

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Mon, 06 Jul 2015 10:20:41 -0400, Denis Beauregard
wrote:

OO and LO accepted for years to enter pre-1900 dates as dates
while Excel considered them as texts. I found a method to
handle that similarly, by prepending a ' to the cell.

So enter '13/1/3119 to keep it as is. I enter all complete dates
like that, even after 1900. In a few cases, the quote is still
shown, when the date is wrong, i.. '12-31-2000 will be right
but '13-31-2000 will show the quote.

Thanks very much, I'll suggest to my wife that she do that with all
fields where numerals are separated with slashes.

We're not going to waste the paper and ink to reprint the ones that
were wrong -- I'll just correct them by pen.


--
Steve Hayes from Tshwane, South Africa
Web: http://www.khanya.org.za/stevesig.htm
Blog: http://khanya.wordpress.com
E-mail - see web page, or parse: shayes at dunelm full stop org full stop uk

On Tue, 07 Jul 2015 09:06:12 -0400, Dennis Lee Bieber
wrote:

On Tue, 07 Jul 2015 04:58:36 +0200, Steve Hayes
declaimed the following:



Thanks very much, I'll suggest to my wife that she do that with all
fields where numerals are separated with slashes.

May or may not help, but...
http://exceluser.com/formulas/earlydates.htm

No, the problem is not early dates, but rather Excel treating
non-dates as if they were dates.

It seems that if it finds one field in a column that looks as though
it could possibly be a date, then it will treat other fields in that
column as dates too, and store them as numerals.

So even if it is formatted to *display* as text, as entered, if one
pronts it, or saves it in a a CSV style, it will print or store the
wrong value.

It seems that the only way out of it is to precede every entry in the
field (column) with a '




--
Steve Hayes from Tshwane, South Africa
Web: http://www.khanya.org.za/stevesig.htm
Blog: http://khanya.wordpress.com
E-mail - see web page, or parse: shayes at dunelm full stop org full stop uk

It seems that if it finds one field in a column that looks as though
it could possibly be a date, then it will treat other fields in that
column as dates too, and store them as numerals.

So even if it is formatted to *display* as text, as entered, if one
pronts it, or saves it in a a CSV style, it will print or store the
wrong value.

So for 1 *last time*...

If you *do not* use Excel's methods to import/output the data, but use
VBA standard file I/O methods OR ADODB, the data transfers *'as is'*
without being changed by Excel.

Using Excel's import methods gives Excel license to *interpret* data
type! As you're experiencing here this is not what you want to happen*!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Wed, 08 Jul 2015 09:03:19 -0400, GS wrote:

It seems that if it finds one field in a column that looks as though
it could possibly be a date, then it will treat other fields in that
column as dates too, and store them as numerals.

So even if it is formatted to *display* as text, as entered, if one
pronts it, or saves it in a a CSV style, it will print or store the
wrong value.

So for 1 *last time*...

If you *do not* use Excel's methods to import/output the data, but use
VBA standard file I/O methods OR ADODB, the data transfers *'as is'*
without being changed by Excel.

Using Excel's import methods gives Excel license to *interpret* data
type! As you're experiencing here this is not what you want to happen*!

So you have to learn VBA and create a VBA routine for keyboard entry
to get Excel to record what you actually typed? And if you don't it
changes what you typed into something else, willy nilly?


--
Steve Hayes from Tshwane, South Africa
Web: http://www.khanya.org.za/stevesig.htm
Blog: http://khanya.wordpress.com
E-mail - see web page, or parse: shayes at dunelm full stop org full stop uk

On Wed, 08 Jul 2015 09:03:19 -0400, GS wrote:

It seems that if it finds one field in a column that looks as
though it could possibly be a date, then it will treat other fields
in that column as dates too, and store them as numerals.

So even if it is formatted to *display* as text, as entered, if one
pronts it, or saves it in a a CSV style, it will print or store the
wrong value.

So for 1 *last time*...

If you *do not* use Excel's methods to import/output the data, but
use VBA standard file I/O methods OR ADODB, the data transfers *'as
is'* without being changed by Excel.

Using Excel's import methods gives Excel license to *interpret* data
type! As you're experiencing here this is not what you want to
happen*!

So you have to learn VBA and create a VBA routine for keyboard entry
to get Excel to record what you actually typed? And if you don't it
changes what you typed into something else, willy nilly?

No! You just don't use Excel's built-in import features. Once the data
is imported using VBA it can be manipulated however you like because
Excel hasn't 'interpreted' its data types during the import process.

IOW, printing your sample data over 5 versions of Excel running on 3
different OSs didn't change how the data printed. I understand that
this is what you want! Or am I mistaken?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I forgot to mention there are many samples of code available for using
a macro to import data to a worksheet, and so you really don't have to
learn how to use VBA to run a ready-made macro. Just assign the macro
to a menuitem or button. You can store it in PERSONAL.XLS so it's
always available.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Wed, 08 Jul 2015 15:19:32 -0400, GS wrote:

I forgot to mention there are many samples of code available for using
a macro to import data to a worksheet, and so you really don't have to
learn how to use VBA to run a ready-made macro. Just assign the macro
to a menuitem or button. You can store it in PERSONAL.XLS so it's
always available.

I was talking primarily about data entry by keyboard.

And it seems that the solution is always to type ' at the beginning of
every cell in a column that contains numerals and slashes but is not a
date, otherwise there is a risk of Excel interpreting and storing it
as a date, so that even though it may *display* correctly, it will not
print or export correctly.




--
Steve Hayes from Tshwane, South Africa
Web: http://www.khanya.org.za/stevesig.htm
Blog: http://khanya.wordpress.com
E-mail - see web page, or parse: shayes at dunelm full stop org full stop uk

On Wed, 08 Jul 2015 15:19:32 -0400, GS wrote:

I forgot to mention there are many samples of code available for
using a macro to import data to a worksheet, and so you really
don't have to learn how to use VBA to run a ready-made macro. Just
assign the macro to a menuitem or button. You can store it in
PERSONAL.XLS so it's always available.

I was talking primarily about data entry by keyboard.

And it seems that the solution is always to type ' at the beginning
of every cell in a column that contains numerals and slashes but is
not a date, otherwise there is a risk of Excel interpreting and
storing it as a date, so that even though it may *display* correctly,
it will not print or export correctly.

Yes, for direct entry this is how I input values I don't want
interpreted. Though, manually typing your data into cells didn't change
in my tests and so I suspect there's a setting somewhere that's causing
printouts to use 2-digit years. Not sure where/how because it doesn't
happen at my end.

Regardless, Excel's attempts at 'helpfulness' can really be annoying at
times!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion