I am importing from an Excel spreadsheet (created from a JDE report)
that stores its dates as yy/mm/dd.

Is there an easy way to convert this into mm/dd/yy in Filemaker?

Any help would be appreciated.

Regards...John B

In article <1172269459.621715.270750 (AT) k78g2000cwa (DOT) googlegroups.com>,
"JohnB" <bilecky (AT) excite (DOT) com> wrote:

If the Excel dates are being imported into a Text field as yy/mm/dd,
then you can have a calculation using the Date function to obtain a
correct date:

Date (Middle(IncomingDate, 4, 2),
Right(IncomingDate, 2),
2000 + Left(IncomingDate, 2)
)

The Date function work in American format of Date(Month, Day, Year).

This assumes two things:

- that there is ALWAYS two digits for the day, month and year,

- ALL years are from 2000 onwards.

If there isn't always two digits, then you need a calculation that is a
little more complicated and usues the position of the "/" characters to
work out what the numbers are:

Date(Date(Middle(IncomingDate, Position(IncomingDate, "/", 1, 1)
+ 1, Position(IncomingDate, "/", 1, 2) -
Position(IncomingDate, "/", 1, 1)),
Right(IncomingDate, Length(IncomingDate)
- Position(IncomingDate, "/", 1, 2)),
2000 + Left(IncomingDate, Position(IncomingDate,
"/" , 1, 1) -1)
)

Whichever calculation you use, it can be either a Calculation field
(with Date result) or an Auto-enter calculation for a normal Date field
(as long as you remember to Perform Auto-enter calculations when
importing the data).



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Thanks, Harry. That did it.

Regards...JohnB

On Feb 23, 7:14 pm, Helpful Harry <helpful_ha... (AT) nom (DOT) de.plume.com>
wrote: