TMG-L ArchivesArchiver > TMG > 2004-12 > 1103595610
From: Carlton Brooks <>
Subject: Re: [TMG] I am Confused
Date: Mon, 20 Dec 2004 19:20:19 -0700
References: <41C76663.firstname.lastname@example.org> <email@example.com>
Great solution. I had written somthing similar using macros and such,
but yours was a lot easier. BTW there is a plug-in to excel that lets
you use pre 1900 dates.
Many thanks. Also love the opening web page of your site.
Joe Makowiec wrote:
> At 04:55 PM 12-20-2004 -0700, Carlton Brooks wrote:
>> I want to create a list of births, death, marriages, etc. using the
>> date format of MM/DD/YYYY. In the LOP report it does not give me an
>> option to change the date.
>> If I export the file to Excel, the date comes through as text, and it
>> considerably hard to manipulate.
> OK - just ran a LOP, and dates come out DD Mmm YYYY, right? What you
> want to do:
> - add 4 columns to the right of the existing text date. We'll assume
> that the date is in column C.
> - in the first column, add this formula: =left(c1,2).
> - in the second column, add this formula: =mid(c1,4,3)
> - in the third column, add this formula: =right(c1,4)
> You should now have four columns: the original text date, the day, the
> month as a 3-letter text string, and the year.
> - Highlight the month column. Do a find/replace (Edit>Find); look for
> Jan and replace with 1, Feb and replace with 2...
> - You now have all-numeric dates
> - in the remaining blank column (I'll assume it's G, and that day is in
> D, month is in E and year is in F): =date(f1,e1,d1)
> Column G should now contain an excel date. Copy all the formulas all
> the way down the column. Now, in Column G, Copy; paste special>values.
> You can delete columns C-F and Column G becomes column C.
> I don't have a copy of Excel handy to test this; it may need a bit of
> tweaking. Also: I haven't used Excel for pre-1900 dates, but as I
> understand it, Excel doesn't play nice with dates pre-c20.