6 Replies Latest reply on Jun 8, 2009 4:10 PM by VenturaDon

# Converting yyyymmdd date field

### Title

Converting yyyymmdd date field

### Post

As a newbie to FP, I just imported 270k records with a date field in yyyymmdd format. To convert to a mmddyyyy date field, I created a calculation field and attempted to follow a previous post:

Date ( Middle ( ASCIIdate ; 5 ; 2 ) ; Middle ( ASCIIdate ; 7 ; 2 ) ; Middle ( ASCIIdate ; 1 ; 4 ) )

Then you can even trasform that calc field into a simple date field ( the calculated date will remains intact )

So in Specify Calculation I inserted:

f18= Date ( Middle ( ASCIIdate ; 5 ; 2 ) ; Middle ( ASCIIdate ; 7 ; 2 ) ; Middle ( ASCIIdate ; 1 ; 4 ) )

but get "The Specified field cannot be found" error msg. f18 is defined as text.

Thanks for the help!

• ###### 1. Re: Converting yyyymmdd date field
When you get this error message, you should see that a part of your calculation is highlighted. What part is highlighted?
• ###### 2. Re: Converting yyyymmdd date field

f18= Date ( Middle ( ASCIIdate ; 5 ; 2 ) ; Middle ( ASCIIdate ; 7 ; 2 ) ; Middle ( ASCIIdate ; 1 ; 4 ) )

Hi Don

I suspect that the name of your field isn't ASCIIdate !

• ###### 3. Re: Converting yyyymmdd date field

So in Specify Calculation I inserted:

f18= Date ( Middle ( ASCIIdate ; 5 ; 2 ) ; Middle ( ASCIIdate ; 7 ; 2 ) ; Middle ( ASCIIdate ; 1 ; 4 ) )

Only this part should go into the formula box:

Date ( Middle ( Yourfield ; 5 ; 2 ) ; Middle ( Yourfield ; 7 ; 2 ) ; Middle ( Yourfield ; 1 ; 4 ) )

Replace "Yourfield" with the name of the field where the YYYYMMDD data is.

• ###### 4. Re: Converting yyyymmdd date field

Thanks for the clarification - I've put the field name in the proper place, but got "weird" result numbers not matching the yyyymmdd f18 field, so I created a test file with 2 fields and one record: f18 ( 20040628 ) and the calc field with:

Date ( Middle ( f18 ; 5 ; 2 ) ; Middle ( f18 ; 7 ; 2 ) ; Middle ( f18 ; 1 ; 4 ) )

which gives the result of 731760, so something's still off...

I'm playing with different combos of numbers in the string to see if I can get this to work!

• ###### 5. Re: Converting yyyymmdd date field
Set the result type of the calculation to Date.
• ###### 6. Re: Converting yyyymmdd date field
Woo hoo - that worked - thank you!!