TMG-L ArchivesArchiver > TMG > 2004-12 > 1103590413
From: Joe Makowiec <>
Subject: Re: [TMG] I am Confused
Date: Mon, 20 Dec 2004 19:53:33 -0500
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
- 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 Makowiec can be reached at: