TMG-L Archives

Archiver > TMG > 2004-12 > 1103590413


From: Joe Makowiec <>
Subject: Re: [TMG] I am Confused
Date: Mon, 20 Dec 2004 19:53:33 -0500
References: <41C76663.1020601@cox.net>
In-Reply-To: <41C76663.1020601@cox.net>


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.

Joe
--
Joe Makowiec can be reached at:
http://makowiec.org/contact/?Joe
http://makowiec.org/

This thread: