Hi krishnanm,
Here are some other formulas... these will interpret the date using your
computer's regional settings, same as if you typed the date in yourself:
=DATEVALUE(B1)
or
=--B1
The trick in the second example is using any arithmetic operation with a
neutral effect. It forces Excel to convert the text to a date serial/date.
In your file, the first date @B1 appears to be recognized as an actual date,
and DATEVALUE generates an error, but the second method will work for both
real dates and text dates.
The TEXT function that was suggested returns a TEXT representation of the
date. If it was in text form to begin with, yes, it will get converted to a
date momentarily (according to regional settings), and then back to text
according to the specified format.
If you want the cell to contain a date, not text, when done, use an
arithmetic operation to insure it is converted to a date, then just format
the cell with the date format you want.
If the text representation of a date is in a different regional
representation than your computer's... for example, d/m/y and your computer
is m/d/y, then I think you need to use some string extraction techniques
such as:
=IF(ISNUMBER(B1),B1,DATE(RIGHT(B1,4),MID(B1,FIND("/",B1)+1,LEN(B1)-FIND("/",
B1)-5),LEFT(B1,FIND("/",B1)-1)))
The above assumes the textual date is in B1, contains slashes as a
delimiter, and is in d/m/yyyy format (must be 4 digit year). It first
checks if it is a number/date already, and returns the value unchanged if
so. Otherwise it converts the d/m/yyyy to a date.
Asa
-----Original Message-----
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of krishnanm2...@gmail.com
Sent: Thursday, November 24, 2011 9:48 PM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Convert text to date using formula...
All,
Is there a formula which converts text to date?
PFA the sample file which I am trying to convert..
Thanks in advance
Best regards!
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
