This still doesn't address his requirement to reverse the day/month to month/day.
In fact, if you do not address this as part of the text string, then you risk corruption the date! If the original data is dd.mm.yyyy and his Windows environment is set up to display dates as mm/dd/yyyy then, when you replace the "." with "/" (whatever method is used) as long as the result remains a string, you're OK, but once you edit the contents, or simply replace the "/" with "/", Excel will attempt to "cast" the text string into it's date format template. In which case, a date like 03-Jan-2013 which WAS displayed as 03.01.2013 will be changed to text "03/01/2013", Excel will "cast" it as mm/dd/yyyy and store the date as 01-Mar-2013 instead of 03-Jan-2013 So, in order to avoid corruption the data values, the month/day MUST be transposed as part of the text string conversion. Paul ----------------------------------------- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley ----------------------------------------- ________________________________ From: Maries <talk2mar...@gmail.com> To: excel-macros@googlegroups.com Sent: Tue, February 19, 2013 11:18:14 AM Subject: Re: $$Excel-Macros$$ Please share inputs to format Date HI, Try it, =TEXT(SUBSTITUTE(A1,".",""),"00\/00\/0000") On Tue, Feb 19, 2013 at 5:14 PM, Paul Schreiner <schreiner_p...@att.net> wrote: A couple of things here: >The original request was a result that changed the "dd.mm.yyyy" format to >"mm/dd/yyyy" >If you were ONLY wishing to replace the "." with "/", then you wouldn't need >the >"nested" substitutes: >=SUBSTITUTE(A3,".","/") >would replace all of the occurrences of the "." > >the =Mid() function, in this case, serves no purpose. > >In addition, using simple string functions would return a result as a TEXT >STRING, not a date. > >By returning a date, then you can change the DISPLAY format however you wish >without changing the VALUE. > >=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)) >=DATEVALUE(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,4)) > >are both viable solutions. > >Paul >----------------------------------------- >“Do all the good you can, >By all the means you can, >In all the ways you can, >In all the places you can, >At all the times you can, >To all the people you can, >As long as ever you can.” - John Wesley >----------------------------------------- > > > > > ________________________________ From: Kuldeep Singh <naukrikuld...@gmail.com> >To: excel-macros@googlegroups.com >Sent: Tue, February 19, 2013 7:53:26 AM >Subject: Re: $$Excel-Macros$$ Please share inputs to format Date > > > > >Hi Harshad, > > >Try this one. > > >=MID(SUBSTITUTE(SUBSTITUTE(A2,".","/",2),".","/",1),1,10) > > >Regards, > >Kuldeep Singh >Info Edge India Limited (naukri.com) >Phone.: +91-0120-4841100, Extn.: 2467, 9716615535 >naukrikuld...@gmail.com || www.naukri.com >Please Consider the environment. Please don't print this e-mail unless you >really need to. > > >On Tue, Feb 19, 2013 at 6:16 PM, Paul Schreiner <schreiner_p...@att.net> wrote: > >are you wanting a VBA solution or an Excel solution? >> >>For Excel: >>If your string is consistent, as the formatting suggests, >>and the date is in cell A1, then you can use: >>=DATEVALUE(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,4)) >> >> >> >>Paul >>----------------------------------------- >>“Do all the good you can, >>By all the means you can, >>In all the ways you can, >>In all the places you can, >>At all the times you can, >>To all the people you can, >>As long as ever you can.” - John Wesley >>----------------------------------------- >> >> >> >> >> ________________________________ From: Harshad K <harshadpl...@gmail.com> >>To: excel-macros@googlegroups.com >>Sent: Tue, February 19, 2013 7:40:20 AM >>Subject: $$Excel-Macros$$ Please share inputs to format Date >> >> >> >>Hi, >> >>I want to convert 18.01.2011(DD.MM.YYYY) to 01/18/2011 (MM/DD/YYYY). >> >>Best Regards, >>Harshad Khandare-- >>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >>https://www.facebook.com/discussexcel >> >>FORUM RULES >> >>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) Jobs posting is not allowed. >>6) Sharing copyrighted material and their links is not allowed. >> >>NOTE : Don't ever post confidential data in a workbook. Forum owners and >>members >>are not responsible for any loss. >>--- >>You received this message because you are subscribed to the Google Groups "MS >>EXCEL AND VBA MACROS" group. >>To unsubscribe from this group and stop receiving emails from it, send an >>email >>to excel-macros+unsubscr...@googlegroups.com. >>To post to this group, send email to excel-macros@googlegroups.com. >>Visit this group at http://groups.google.com/group/excel-macros?hl=en. >>For more options, visit https://groups.google.com/groups/opt_out. >> >> >> >>-- >>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >>https://www.facebook.com/discussexcel >> >>FORUM RULES >> >>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) Jobs posting is not allowed. >>6) Sharing copyrighted material and their links is not allowed. >> >>NOTE : Don't ever post confidential data in a workbook. Forum owners and >>members >>are not responsible for any loss. >>--- >>You received this message because you are subscribed to the Google Groups "MS >>EXCEL AND VBA MACROS" group. >>To unsubscribe from this group and stop receiving emails from it, send an >>email >>to excel-macros+unsubscr...@googlegroups.com. >>To post to this group, send email to excel-macros@googlegroups.com. >>Visit this group at http://groups.google.com/group/excel-macros?hl=en. >>For more options, visit https://groups.google.com/groups/opt_out. >>-- >Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >https://www.facebook.com/discussexcel > >FORUM RULES > >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) Jobs posting is not allowed. >6) Sharing copyrighted material and their links is not allowed. > >NOTE : Don't ever post confidential data in a workbook. Forum owners and >members >are not responsible for any loss. >--- >You received this message because you are subscribed to the Google Groups "MS >EXCEL AND VBA MACROS" group. >To unsubscribe from this group and stop receiving emails from it, send an >email >to excel-macros+unsubscr...@googlegroups.com. >To post to this group, send email to excel-macros@googlegroups.com. >Visit this group at http://groups.google.com/group/excel-macros?hl=en. >For more options, visit https://groups.google.com/groups/opt_out. > > > >-- >Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >https://www.facebook.com/discussexcel > >FORUM RULES > >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) Jobs posting is not allowed. >6) Sharing copyrighted material and their links is not allowed. > >NOTE : Don't ever post confidential data in a workbook. Forum owners and >members >are not responsible for any loss. >--- >You received this message because you are subscribed to the Google Groups "MS >EXCEL AND VBA MACROS" group. >To unsubscribe from this group and stop receiving emails from it, send an >email >to excel-macros+unsubscr...@googlegroups.com. >To post to this group, send email to excel-macros@googlegroups.com. >Visit this group at http://groups.google.com/group/excel-macros?hl=en. >For more options, visit https://groups.google.com/groups/opt_out. > > > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
