View Full Version : Convert number to date format
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.