Hi..
I am currently in trouble converting number to date format. I have a set of number for example : 110930 (text formatted, 6 digits)which represent 30 SEPT 2011.
Could any one give me a way out to convert the number into DD-MONTH-YY (30-SEPT-11)?

Thank you very much, indeed

What are you using for this conversation process.
Is this on a spreadsheet or in Windows ?

This depends on the software. Without knowing that, we can't provide a function to do it. Most spreadsheet or database software products will have functions to parse this out

I am using ms. access 2002 - 2003 format.

=DateSerial(Left([field]),Mid([field],3,2),Right([field],2))

You may have to convert the field to text first.

Already converted the field (F2) into text, typed the formula:

DateSerial(LEFT(F2,2),MID(F2,3,2),RIGHT(F2,2))
But it results #NAME?

Why is that?

Where did you type it?

On the formula bar, in excel spreadsheet.

You said you were using Access, not Excel. Excel doesn't have a DateSerial function, the equivalent is the Date() function.

You you... my fault.. :)

It Works!! I changed the DateSerial function into Date function in spreadsheet, and it turned the date format I wanted.

Thanks guys for your help...

I'm glad you got it. But for future reference you can see how asking a complete question and posting in the right place can make a difference. You could have had this answer yesterday morning, if you had correctly given the software you were using. Or if you had originally posted in the Spreadsheet forum instead of the Windows forum we would have offered the correct answer.

Same problem guys. I have 110113 in cell F2 (already in text format, in Excel). I want to change it to DATE format, so

=DATE(LEFT(F2),MID(F2,3,2),RIGHT(F2,2), but it turned to 1/13/1901. It is supposed to be 1/13/2011, right?

any advice on this?

thanks

Try

DATE(2000 + Val(LEFT(F2,2)),MID(F2,3,2),RIGHT(F2,2), but it turned to 1/13/1901

Did not work. It turned #NAME?

formula I typed : =DATE(2000+Val(LEFT(F2,2)),MID(F2,3,2),RIGHT(F2,2) )

:(

YEAY! It works! I typed VALUE, instead of Val..

thanks a lot Scott :)

You're welcome. The point is that you had to set the parameter for the full 4 digit year.

Now the problem is if you have any dates in the last century.