Converting Mar-29-02 to 3/29/02 or 03/29/02

[nightdesigns]

I get dates in this format Mar-29-02, FM wants them in a // format. What's the easiest way to do this. It doesn't matter if the 0 is preceeding the number except for the year. thanks.

[ March 29, 2002, 03:54 PM: Message edited by: nightdesigns ]

[bobsmith]

You could do a calculated replace, however if possible I would put into excel and reformat the dates and then import into FM. I would suggest using the 4 digit year if you reformat in excel to prevent problems when you import into FM. If you would like the calculations to do in FM let me know.

I just checked again and the Mar-29-02 is an acceptable format in filemaker 5.0 so if your field is formated as date you shouldn't have a problem. You can change the format.

[danjacoby]

When you say you "get dates in this format...", in what form are you receiving this data? Is it in an Excel spreadsheet, a Word document, or what?

[danjacoby]

quote:

---

Originally posted by bobsmith:

I just checked again and the Mar-29-02 is an acceptable format in filemaker 5.0

---

No it isn't -- I have FMP 5.0v3 and it doesn't accept this input.

[bobsmith]

Dan. I guess we should never say something works always. I have a excel (Windows) spreadsheet that is formated Mar-29-2002 (mmm-dd-yyyy) and it imports into a filemaker application with no problem. The field I am importing into is defined as a date field. I can then change the format to mm/dd/yy in Filemaker. Possibly it is because the data was originally entered into excel as 3/1/02, I don't know.

[nightdesigns]

quote:

---

Originally posted by danjacoby:

When you say you "get dates in this format...", in what form are you receiving this data? Is it in an Excel spreadsheet, a Word document, or what?

---

The date comes in an e-mail (not the header, but in the body of an e-mail from an external source which i have no control over). FM is going to be recieving 100's of e-mail's a day so manually converting each in excel isn't possible. And i already have 7000+ records in the mm/dd/yy format.

Thanks

-j

[RussBaker]

Once you get your date in the Mar-29-02 format, get it into a text field called "DateText" and then have a "ConvertedDate" field which =

code:

---

Date(

Case(LeftWords(DateText, 1)="Jan",1,LeftWords(DateText, 1)="Feb",2,LeftWords(DateText, 1)="Mar",3,LeftWords(DateText, 1)="Apr",4,LeftWords(DateText, 1)="May",5,LeftWords(DateText, 1)="Jun",6,LeftWords(DateText, 1)="Jul",7,LeftWords(DateText, 1)="Aug",8,LeftWords(DateText, 1)="Sep",9,LeftWords(DateText, 1)="Oct",10,LeftWords(DateText, 1)="Nov",11,LeftWords(DateText, 1)="Dec",12),

TextToNum(MiddleWords(DateText, 2, 1)),

TextToNum(If(TextToNum(RightWords(DateText, 1))<10,"20","19")&RightWords(DateText, 1)))

The "<10" in the last line is the divider which allocates the year to either the 1900s or 2000s. Years less than "10" will be made into 20xx and others into 19xx. Change this number if you need to.

The use of the "Words" functions gets around any issues caused by single digit or double digit months and years in case leading zeroes have been stripped away.

Russ Baker

[ March 30, 2002, 06:10 PM: Message edited by: Russell Baker ]

[nightdesigns]

quote:

---

Originally posted by Russell Baker:

Once you get your date in the Mar-29-02 format, get it into a text field called "DateText" and then have a "ConvertedDate" field which =

code:

---

Date(

Case(LeftWords(DateText, 1)="Jan",1,LeftWords(DateText, 1)="Feb",2,LeftWords(DateText, 1)="Mar",3,LeftWords(DateText, 1)="Apr",4,LeftWords(DateText, 1)="May",5,LeftWords(DateText, 1)="Jun",6,LeftWords(DateText, 1)="Jul",7,LeftWords(DateText, 1)="Aug",8,LeftWords(DateText, 1)="Sep",9,LeftWords(DateText, 1)="Oct",10,LeftWords(DateText, 1)="Nov",11,LeftWords(DateText, 1)="Dec",12),

TextToNum(MiddleWords(DateText, 2, 1)),

TextToNum(If(TextToNum(RightWords(DateText, 1))<10,"20","19")&RightWords(DateText, 1)))

The "<10" in the last line is the divider which allocates the year to either the 1900s or 2000s. Years less than "10" will be made into 20xx and others into 19xx. Change this number if you need to.

The use of the "Words" functions gets around any issues caused by single digit or double digit months and years in case leading zeroes have been stripped away.

Russ Baker

---

I set myself up a test file with DateText as text and ConvertedDate as a date. i put your formula in as a calculation for ConvertedDate and to evaluate only when all fields are full. Unfortunatly I keep getting 11/30/0019 regardless of what's in the DateText box. Ideas?

Thanks

-j

[RussBaker]

I can't see why it would give this answer. I've emailed you the original file I tested the solution on before I posted it. Hope that works - it still does for me. There shouldn't be any typos, I copied the calculation straight out of FileMaker and pasted it into the forum???

Russ

[RussBaker]

OK - give me an email if this persists after the file I sent you. If you type a blank space - SPACEBAR - into the DateText field then you will get 30 Nov 0019. But you can still ad a date in your original Mar-29-02 format and the calc will sort it out.

In the define calculation, make sure that "Do not evaluate if all referenced fields are empty" is checked.

Are you confident that your input dates will be in the format you described. If there are extra spaces at the start, end or around any of the "-" in the middle, that will still be OK.

Russ