Date formating via calculation

[futurepacer]

Hi all

Have tried all sorts to sort this one... also can't find a solution on this forum - hence the post.

I have imported data from email packages. Unfortunately the dates in the same field are in different formats. One set is dd/mm/yyyy and the other is mm/dd/yyyy. This leads to some interesting dates eg: 08/13/2006. I would like to convert them into dd/mm/yyyy.

The first part of my calc is If(mainDate(month>12) so that the conversion only takes place with the incorrectly formatted dates but I can't work out the conversion calc that follows

Any thoughts?

Thanks for taking the time to consider this

Best wishes

john

[comment]

If you have imported into a DATE field, you will have to start over. A record containing 01/03/2006 in mm/dd/yyyy format will import into a file set to dd/mm/yyyy without a problem - except it will be read as March 1. So you need to convert ALL of them, not just the obviously wrong ones.

Moreover, a record containing 08/13/2006 will be imported as invalid date. An invalid date has no month. OTOH, a valid date can never have month > 12.

A simple way to handle this is to set your OS settings to mm/dd/yyyy, set the file to use system settings, and import the mm/dd/yyyy file. Then reset everything back to dd/mm/yyyy and import the other file. Of course, this is assuming you only need to do this once in a blue moon. Otherwise you should set up a scripted routine importing into a TEXT field and converting that.

[futurepacer]

Hi comment

Thanks for the response

I had hoped to solve my problem using something similar to JMO's solution below:

-----------

10/20/06 11:02 PM - Post#226232

In response to selzlerb

Show all the records and then select the MYTABLE::date field so the cursor is blinking in it. Use the formula below in a Replace Field Contents calculation after you have made a backup.

Date(Middle(MYTABLE::date ; 5; 2); Right(MYTABLE::date; 2); Left(MYTABLE::date; 4))

------------

but within a calc field or series of calcs. Have experimented and produced some interesting results, none of which were correct!

Your solution makes sense. As it's not a one-off situation I'll explore the scripting solution you suggest.

Thanks agaion for your interest

Best wishes

john

[comment]

The transformatiom itself is easy - provided that (1) you have the datestring in a TEXT field, and (2) you know if it needs to be done or not. You should have a script for each imported format (or one script that branches according to the selected format). For example, if the format matches, import directly into a date field. If not, import into a text field first, then do the transformation.