Jump to content

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


This topic is 7139 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 ]

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 7139 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.