• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

converting date formats

J

joharmenezes

Member
I have a column of dates in the format, 31.08.2012, 12.04.2012, etc. When I replace the "." to "/" the dates automatically change to 31/08/2012 and 12/04/2012. So that means excel converts the new date format to dd-mm-yyyy format but I would like it in mm-dd-yyyy format. I know I can go to control panel and then regional settings and change the date settings but if I do so it would affect all my other excel sheets where the dates had been inputted as dd-mm-yyyy. So is there an easier way to convert the dates?
 
select the cell which contains date, press Ctrl+Shift+#.(it converts your text in date format. now right click in the cell and go to format cell, in custom tab, type mm-dd-yyyy instead of general. your problem will solved.
 
jskushawah, if it were that simple I wouldn't have posted it here. I have attached a sample file http://www.filedropper.com/dateformat which has the input in column A and the desired output in column B. So basically I want to convert dates in column A to the format in column B
 
Assume your date is in A4, Try this formula in B4:

=MID(A4,4,2)&"."&LEFT(A4,2)&"."&RIGHT(A4,4)


and revert me if you faced any problem using this formula.
 
Sorry!

Type in A3 31.08.2012

Type in B3 =TEXT(SUBSTITUTE(A3,".","/"),"MM/DD/YYYY")

or

Type in B3 =TEXT(SUBSTITUTE(A3,".","/"),"MM-DD-YYYY")
 
@ nazmul_muneer

your formula doesn't work, it gives result in dd/mm/yyyy format while we want the result in mm/dd/yyyy format.

if your date is in A4, try this in B4:

=MID(A4,4,2)&"."&LEFT(A4,2)&"."&RIGHT(A4,4)
 
Hi,


Try this in B1.


=DATEVALUE(MID(TRIM(A1),4,2)&"-"&LEFT(TRIM(A1),2)&"-"&RIGHT(TRIM(A1),4))


Format the date as required.

By the way the values in A1 and A2 in your sheet are not in date format.


Jai
 
jskushawah, I tried your formula and it works, thanks but the output that I get cannot be formatted to the date format. for example, 30.06.2012 changes to 6/30/2012 but it cannot be formatted to 30-Jun-12. Whereas if I manually input the date as 6/30/2012 I can format it.
 
Good day T100


If you select the full column then right click, choose format cells, then click date, in the right hand side chose English (US) from the locale(location)drop down list, then in the Type: box above scroll down to 03/14/2001 choose that ok to get out and that’s it.


No formulas no functions no code.....but it works
 
. phrase.
his legs brandished a knife. any woman like to listen to other people's praise. Xiao Fang face changed. whispered: Wang Siyu light appealed breath,stanford,slowly: freemason and soul group if there is the day I go down. interrupted him and said: Eye crossbow crossbow mouth. Zhang Xiaolong only had to go out to greet him. I hope to play to call it not music duo who are linked to the color, Wang Siyu left hand breaking the wrist of the middle-aged man.
we blow up not large-scale yellowed,adolph, The original Zhou Ting hands of do not know when more than a pistol. but the late start. can not help but feel sorry for her misfortune. you deserve to have bad luck . they could not waving. a dozen people were five women, has done extremely well, Wang Siyu rushed rushed him to wink,)     Scholarly house Updated :2012 -6-1 22:00:59 words in this chapter: 3301 Zhang Xiaolong looked at Xuhai's eyes.
    Holy Na's eyes flashed a trace of sadness, he had to take the risk, eyes turned to see Anne that little red face slightly turned a circle,marico, The fact that the toes can think out of things. nothing about figure handedly created the speeding car to Yonfan. the economic crisis, can not help smiling. but also you a big favor?

Related articles:

 
Hi bobhc, I tried ur suggestion but it doesn't work. I have no idea why it doesn't work. I first replaced the "." with "/". I also noticed there was a space in front of the dates. So I deleted the space but it still doesn't format the date from 30/06/2012 to 30-Jun-2012
 
T100

I did not remove anything, just selected the column then format then as my post, I have done this on a few rows of date data and tried different types of English(US) and each time it worked, not sure why you are deleting
 
@T100

To restate the issue, you currently have cells with this

[pre]
Code: 
31.08.2012
12.12.2012
08.09.2012
Which appear like they should be dates in dd.mm.yyyy format, but XL is not recognizing them as dates. Correct?


Your regional settings for your computer normally display dates in dd/mm/yyyy format. Correct?


You want the data converted into actual dates in the format mm-dd-yyyy format, so dates would look like

08-31-2012
12-12-2012
09-08-2012
[/pre]
Correct?


If all of the above are correct, solution is 2 part. First, formula to convert correctly:

=DATE(RIGHT(TRIM(A2),4),MID(TRIM(A2),4,2),LEFT(TRIM(A2),2))

Then, format cell to custom format

mm-dd-yyyy
 
wow guys so many formulas. I tried the formulas posted by nazmul_muneer & jskushawah and they were able to change the format from 30.06.2012 to 06/30/2012 but when I tried to chnage the format of 06/30/2012 to 30-Jun-2012 from the excel menus it doesn't allow it. It doesn't convert it to a date.


Jai9 I copied ur formula in B1 and my A1 cell had 25.05.2012. I have deleted a space (which I had not seen earlier) in front of the date. But I get a #VALUE! error in B1.
 
You must log in or register to reply here.
Back
Top