Reformat DATES

[KATHWUN]

I inherited a dBase with dates input in various formats. I want to standardize to mm/dd/yy.
This format works for new dates entered, but does not convert the old existing dates. What do I need to know?
Do it manually would be nuts.
TIA
Kath

[PaddyD]

How 'various' are your formats? I'm guessing that you've essentially got text fields, with "20-May-2001", "20-05-01", "20/5/01" and so on. DO most of hte entries have a delimiter (/ or - )?

Paddy

[ This Message was edited by: PaddyD on 2002-05-15 15:09 ]

[KATHWUN]

Paddy,
The formats were input consistently with m/d/yy, using the "/" delimiter. I'm looking to convert to mm/dd/yy.
You question makes me remember that we copied the dbase over from Access into Excel. Could that have changed the dates to text fields?
Do you see a solution? I hope. . .TIA

[Yogi Anand]

On 2002-05-15 15:57, KATHWUN wrote:
Paddy,
The formats were input consistently with m/d/yy, using the "/" delimiter. I'm looking to convert to mm/dd/yy.
You question makes me remember that we copied the dbase over from Access into Excel. Could that have changed the dates to text fields?
Do you see a solution? I hope. . .TIA

Hi KATHWUN:
You ought to coerce the imported dates into numbers by either multiplying with constant 1 or adding a constant 0 to each one.

Please post back if it works for you ... otherwise explain a little further and let us take it from there.

Regards!

_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard code signature line

[ This Message was edited by: Yogi Anand on 2003-01-19 17:12 ]

[Mark W.]

Select the dates, choose the Data | Text to Columns... menu command, press [ Finish ], and then apply a desired date format.

[PaddyD]

Not quite an answer, but you might find the code useful...(Sorry this took a while, but I had to dig the code out from an old file)

If you've got a bunch of 'text' dates with a reliable delimiter (eg 10/05/2002 or 5/5/01 in the order days months years) you can extract the relevant portions using the following:

days:
=MID(A1,1,FIND("/",A1,1)-1)

months:
=MID(A1,FIND("/",A1,1)+1,(FIND("/",A1,(FIND("/",A1,1)+1))-1)-FIND("/",A1,1))

years:
=MID(A1,FIND("/",A1,(FIND("/",A1,1)+1))+1,(LEN(A1)-FIND("/",A1,(FIND("/",A1,1)+1))))

All the code does is use various combintations of find & len to determine the start positions for the mid function by finding the position of the relevant delimiters.

Once you've extracted the day month & year values, you can then fiddle with them to your heart's content.

As always, there are no doubt more elegant ways to do this.

Hope this help
Paddy

[KATHWUN]

To MARK W and PADDYD
Mark's solution worked -- and FAST.
Paddy, I genuinely appreciate your digging into the files for that amazing solution. Occam's Razor made me try Mark's first and, VOILA -- I thank both of you Brains-on-Tap!