OVER 2 YOU, 065 (29/01/02)

EDUCATING EXCEL

I am compiling a database of names with dates of birth and death etc. in Excel. I am using Excel for compiling my family tree, it being much more suited to the task than any commercial software I've found. Unfortunately, Excel will not allow dates to be shown pre-1900 in the database, so I am unable to calculate longevity automatically. Is there any way to sidestep this to allow pre-1900 dates? If not (and I suspect that will be the case) can anyone suggest another way?
Dave Reid, via email

Having had the same problem in production of my own family tree, I simply created separate columns for day, month and year and treated these as standard numerical entries. It means a little more work on the keyboard and there may indeed be a better way - but it works for me!

Wren Gentleman,

Two possible approaches may help. One is to purchase Corel Quattro Pro 8, whose date function starts at 1600 unlike that in Microsoft Excel, this is not expensive. The other is to try John Walkenbach’s spreadsheet site. www.j-walk.com/ss/excel/files/xdate.htm  This tells you all about an add-in called XDATE but it only works with Excel 97 or Excel 2000 and from 1752. In the US the only people apparently interested in history are the Mormons but they do not seem to have influenced Microsoft either.

John Hooton,

The answer is to use Julian Days as used by astronomers. These are an accurate count, in days, from Noon, January 1st 4713 B.C., taking into account the changes in the calendar, including for example, the eleven days lost in the U.K. in 1752. Any book on astronomical time calculation will contain algorithms to change from Calendar Date to Julian Day, & vice-versa. I would cite Peter Duffett-Smith, "Practical Astronomy with your Calculator" Cambridge University Press.  A good starting point on the web would be

Incidentally, Excel (at least up to Excel 97) is in error in  the "serial number" function. If you happen to use a date between Jan 1 1900 & the end of Feb 1900. Someone at Microsoft thought 1900 was a leap year, which it was not.

Harry Metcalfe,

The following function will allow Excel to manipulate dates from 100AD to
9999AD.
Load spreadsheet containing database
Click Tools-Macro-Visual Basic Editor
Click Insert-Module
Type the following lines into the box choosing one of the two lines
commencing 'Aged =' depending on the output you require

Function Aged(Born As Range, Died As Range) As String
Dim LastBirthday, Years, Days
    LastBirthday = IIf(DateSerial(Year(0), Month(Born), Day(Born)) <
DateSerial(Year(0), Month(Died), Day(Died)), Year(Died), Year(Died) - 1)
    Years = LastBirthday - Year(Born)
    Days = DateValue(Died) - DateSerial(LastBirthday, Month(Born),
Day(Born))
    Aged = Years & " years " & Days & " days" 'eg 62 years 240 days
    Aged = Years & "." & Days                     'eg 62.240
End Function

Click File - Close to return to the spreadsheet
Now enter the dates for Born and Died in cells -  use normal windows formats
eg: 21 Jun 1666 or 21/6/1666, but do not format them as date/time, leave them formatted as General Select the cell for the age, also formatted as General
Click on the = (edit formula)
Click on the down symbol beside DATEVALUE
Click on 'more functions'
Select function 'category = user defined'
Select 'Aged' and click OK
Click in the box labelled Born
Click in the cell containing the Born date
Click in the box labelled Died
Click in the cell containing the Died date
Click OK in the box

The appearance could be any combination of those below

Born 5/7/1605 Died 1 Mar1668 Aged 62 years 240 days
Born 5 July 1605 Died 1/3/1668 Aged 62.240
Norman Wilcox,

If the function DATE (year,month,day) is used and year is between 0 (zero) and 1899 (inclusive), Excel adds that value to 1900 to calculate the year. For example, DATE(100,1,2) returns January 2, 2000 (1900+100).  Then any calculations will still function correctly. Sorting in chorological order seems to work, and one could display the formula in the sheet, which will show the correct date. =DATE(1872,1,1

Mark Belfield,

The Gregorian calendar repeats every 400 years. Thus a solution to Dave Reid's problem is to add a multiple of 400 to the year before using dates  pre 1900. Thus, if all dates are after 1600 one adds 400, if dates are after 1200 one adds 800 and so on.

Assume that the day is in column A, the month in column B and the year in column C (all three as numbers).

The formula "=Date(C5*n*400,B5,A5) - Date(C6*n*400,B6,A6)" will give the number of days between the dates in rows 5 and 6. ("n" is a whole number indicating the multiple of 400 by which the years were shifted). If a number of days are required to be added to a date then the appropriate adjustment is made at the end.

The formula "=Year((Date(C5*n*400,B5,A5) + 500) - n*400) gives the year for a date 500 days later than the date in row 5. The day and month are given by "=Day(Date(C5*n*400,B5,A5) + 500)"
and "=Month(Date(C5*n*400,B5,A5) + 500)" respectively.

Note that dates need to be according to the Gregorian calendar, so will need adjusting if before c. 1750
Joseph O'Driscoll, Weybridge, Surrey

It is easy to make up a routine which will convert dates before 1/1/1900 into negative date numbers which are compatible with the date number system used with post-1900 dates. According to Microsoft, Jan 1st, 1900 was day number 1. To see the date number, which Excel assigns to any post-1/1/1900 date, type the date into a cell and hit return. Highlight the cell and use Format>Cells>Number>OK. The date will be transformed into the number of days from the start of the 20^th century. In a simple routine which I have tested (but is not necessarily the most elegant) you should put the day, month and year in adjacent cells. You can perform the calculations in hidden columns somewhere on the right of the spreadsheet and return the result to the main visible section.

For dates before 1/1/1900:

Step 1.

Calculate the approx. number of days, from the year in question to 1900, using the formula: ‘=INT(365.25*(1900-Y))’,  where Y is the year, e.g. for 1875 the result would be 9131

Step 2.

Find the date number for the same day and month in 1900, as described above, e.g. for 12^th June the number would be 164

Step 3.

Calculate the negative date number as follows:

=365 - result #1 - result #2

The date number is -8930 for the example of 12/6/1875

If one wishes to preserve the 3-cell date entry system for dates after 1/1/1900, the last step has to be modified a little. For the new example date, 5/1/2002, the results of the various steps are:

Step 1.  -37256, 

Step 2.   5,

Step 3.  change the formula to: ‘= result #2 - result #1         result: 37261’

Date numbers obtained by these methods, or by the conventional method for post-1900 dates, can be manipulated at will to calculate elapsed times, ages, etc. throughout the centuries.

P.M. Crosse, Bristol

Excel was devised with business-people, not historians, in mind; so its dates are stored as a count of days since the beginning of 1900 (or 1904 if you run the Apple Mac version), which is about adequate for business purposes.  The
count runs out at the end of 2078, providing our grandchildren with their very own millennium bug problem.

There are probably cleverer ways put dates on your family tree in Excel, but this is the way I work round the restriction.

First split the date into two elements, let us say in columns A and B.  In A, enter the date as day, month and 2-digit year, formatting the column to display day and month only (right justified, to look prettier). The century will be wrong, but this will not matter as you don't see it.  In column B, type in the correct year on its own and display it as a numeric integer - not as a date. The result should now look correct, e.g.:  "|  7 Nov,|1873|".

Now add a third column, say C, with a formula that in C2 should appear as:
        =B2+(A2-DATEVALUE("1jan"&YEAR(A2)))/1000

The effect will be displayed, for the above example, as "1873.310", meaning 310 days after January 1st, 1873.  Not very scientific, perhaps, but suficient to allow you to subtract dates from one another and give people's age at death and suchlike with reasonable accuracy.  Of course, if your dates go back to 1751 you will encounter further complications, with the change of calendar.

Since the pseudo-decimal date representation looks a bit inelegant, you'll
probably want to "hide" column C (or whatever) when printing out your data.
John E.S.Driver,

If he breaks down dates of birth and death into three columns, instead of one, so there is a column for day, month and year then he can use a formula to calculate the age at death, or years married if he has the dates entered (see example).
Columns can be added by selecting Insert, Columns.
He can break his existing dates entered down with the DAY MONTH and YEAR functions (see example). He might find it easier to use one of the many commercial software packages
such as Family Tree Maker, the one my family uses.
Phil Andrews,

HEARING AID

I am deaf in one ear, but like to use an MP3 player while working out at the Gym.  Can anyone suggest some software or a way to record tracks onto my MP3 player in mono - or with the "Balance" control adjusted in favour of my good ear? I currently use RealPlayer as my "Jukebox" running on a Dell laptop.

Simon Holloway, via email

I can suggest two Windows based programs that can convert MP3s into mono sound from stereo. "Total Recorder" (available to download as trial version, under 1MB, from www.download.com) (registration around £20 I think) will convert MP3s to mono format, saving of files is possible using the trial version. "Cooledit 2000" (also available as trial version, 9MB, from www.download.com) (registration $69 I think), is full of editing tools, hence more expensive, but will batch convert MP3s to mono sound, and can also convert music files from stereo sound to right or left only. Saving of files is limited to 1 minute of music using the trial version.

Rob Wilmshurst,

Buy a pair of cheep mono headphones or a stereo-to-mono minijack adaptor
from Tandy.

Anthony Ames,

SCOPE FOR IMPROVEMENT

Does anyone know of any software or hardware that can be used to turn a PC into an oscilloscope?

R. Kenny, Edinburgh

Pico Technology makes 'virtual instruments' - basically they're analogue-to-digital converters - that plug into a PC parallel port. With the software provided, the same instrument can be used as oscilloscope, spectrum analyser, multimeter and datalogger. Nice toy!

Prices range from about £70 to £500, depending on bandwidth required. Stockists include www.maplin.co.uk and www.altecweb.com.
Richard Clarke,

ALL IN THE MIND

I sometimes hear in my head music that I have never heard, but I can't read and write music so my melodies go lost.  Is there any software that puts into written music, a tune I whistle or hum?

Franco Cavallini, via email                          

Yamaha's XG Works software includes a facility for singing music into its sequencer so that it can be processed and replayed by a PC. XG Works costs just over 100 US dollars (less than £75) including postage. The XG homepage is at http://www.yamaha.co.uk/xg/index.htm. A free demo version can be downloaded but it doesn't allow saving or printing of any music, which the full version does.
George Baldwin,

Another solution to Mr Cavallini's problem would be to purchase a Dictaphone, carrying it with him at all times. Providing he is not in too much of a hurry, he could then forward the mini cassette to me, and I would write the melody lines down for him.
Daniel Tibbles,

ANALOGUE COMPUTERS

I would like to know if anyone has any information of knows where I could obtain plans for building an ‘analogue’ computer. I understand these were developed in the 1950s, long before the digital computers we use today. If possible or practical I would like to build one for a school science project.

D. Jackson, via email

Way back in olden times (the late 1980's, actually) either the University of Aberdeen or Robert Gordon's Institute of Technology (now the Robert Gordon University) included the construction and operation of an analogue computer in their electronic engineering course. This was no ordinary stand-alone beast: it interfaced to the BBC Micro via the 1MHz bus. Details of the hybrid were published in one of the UK's hobbyist magazines, possibly the much missed Electronics Today International, now incorporated in Everyday Practical Electronics.

I have mislaid my copies of the project articles, but your readers may have some luck with their readers and/or technical staff: contact epe@wimborne.co.uk.

Alternatively, contact the relevant department at AU or RGU. Maybe EPE could update the project to work on a PC?
Roddy MacKenzie,

CAN YOU HELP?

A few years ago I remember reading an article in The Telegraph about a young boy who made a windmill from a discarded bicycle dynamo. It produced enough power to run his radio and provide some light for his garden shed. Can anyone provide plans or tell me how this was done?

D. Modaher, Southall, London

We have a great deal of information and games, which we still wish to access, on floppy discs from a BBC B computer, which has long since passed away!  Does anyone know if it is possible to connect a Cumana twin drive unit (a and b) to a Siemens Nixdorf PC with a 486 processor, 32 Mb RAM running Windows 3.1?

Ailsa M. Collie, via email