One best and quickest way to convert a string based d/m/y format to a date
based m/d/yyyy is by using the text to column feature in excel.
How to do it?
Select the Text to Column wizard, click on Delimited, Next twice, and now
in the column data format, select d/m/y and click on Finish. That would
change all your d/m/y formats to logicall date formats. For example
26/2/2011 (text) will be turned to 2/26/2011 (date)
Regards,
Sam Mathai Chacko
On Sat, Nov 26, 2011 at 2:28 PM, Asa Rossoff <a...@lovetour.info> wrote:
> 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
>
--
Sam Mathai Chacko
--
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
