Change text to date format

[FrankRomero]

I have text that is 60410 for June 4, 2010. I want to update it to read as date, prefereably, 06/04/10.

Also, I want it to work when the dates become 100110. Please help! thanks.

 

[namliam]

This will turn it into a proper date:

dateserial ( Right ( yourfield,2), mid( right('0' & yourfield,8), 3,2), left( right( '0' & Yourfield,8),2) )

 

[FrankRomero]

Nice, but it put the month where I wanted the date - result = 4/6/2010, but I wanted 6/4/2010. I'm sure it's easy, but my brain is already fried.

 

[raskew]

Hi -

Try playing with this from the debug (immediate) window:

x = "60410"  [I](5 digit text number)[/I]
y = "120410"  [I](6 digit text number)[/I]
? dateserial(right(x,2), left(x, len(x) -4), mid(x, len(x)-3, 2))
6/4/2010 
? dateserial(right(y,2), left(y, len(y) -4), mid(y, len(y)-3, 2))
12/4/2010

It appears to function equally well using integers vs. text numbers.

HTH - Bob

 

[namliam]

Nice, but it put the month where I wanted the date - result = 4/6/2010, but I wanted 6/4/2010. I'm sure it's easy, but my brain is already fried.

Well if I put month and day reversed, then reverse them back:
dateserial ( Right ( yourfield,2), left( right( '0' & Yourfield,8),2) , mid( right('0' & yourfield,8), 3,2))

@Bob
Thanks for showing a different but same solution.