Check out some similar questions!

nurmala · Oct 10, 2011, 11:38 PM

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

Curlyben · Oct 10, 2011, 11:50 PM

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

ScottGem · Oct 11, 2011, 03:54 AM

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

nurmala · Oct 11, 2011, 06:39 PM

I am using ms. access 2002 - 2003 format.

ScottGem · Oct 11, 2011, 07:07 PM

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

You may have to convert the field to text first.

nurmala · Oct 11, 2011, 07:30 PM

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?

ScottGem · Oct 11, 2011, 07:38 PM

Where did you type it?

nurmala · Oct 11, 2011, 07:44 PM

On the formula bar, in excel spreadsheet.

ScottGem · Oct 11, 2011, 07:53 PM

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

nurmala · Oct 11, 2011, 08:03 PM

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...

ScottGem · Oct 12, 2011, 03:11 AM

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.

nurmala · Nov 24, 2011, 07:05 PM

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

ScottGem · Nov 24, 2011, 07:40 PM

Try

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

nurmala · Nov 24, 2011, 07:45 PM

Did not work. It turned #NAME?

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

:(

nurmala · Nov 24, 2011, 07:49 PM

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

thanks a lot Scott :)

ScottGem · Nov 25, 2011, 06:14 AM

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.