convert month name & Day & year to day/month/year

[newbiexls]

Dear XLS Gurus,

i was wanting to convert a date in format "May 3 2012 12:00AM" to 03-05-2012 or at least 05-03-2012 , is this possible?

could someone provide this newbie a little help?

thanks in advance,
regards

 

[texasalynn]

if it is a true date and not just text, you can format the cell. Right click on the cell and select format cells. Select date and choose the format you want

 

[Jeff Meyers]

You can use custom formats to present the dates in any way that you prefer.

Row 1 contains the number serial number 41032 which is the date May 3, 2012
Row 3 shows that number / that date in a variety of formats
Row 7 shows the custom formats that I used in Row 3 of that column

Excel Workbook
	A	B	C
1	41032	41032	41032
2
3	May 3 2012 12:00 AM	03-05-2012	05-03-2012
4
5	Row 3 custom formats:
6
7	mmmm d yyyy h:mm AM/PM	dd-mm-yyyy	mm-dd-yyyy
8
9
10	--------------------------------------	--------------------------------------	--------------------------------------
11
Sheet1

Excel 2010

Cell Formulas
Range	Formula
A3	=+A1
B3	=+B1
C3	=+C1

 

[newbiexls]

Hi guys,

thanks for the quick Reply.

it seems that the date is in TEXT format because formatting with custom and changing the display type from different time formats wont change anything on the result :|

 

[Jeff Meyers]

Try one of these formulas to convert your date as TEXT to a date value

Assumption is that your TEXT date is consistently in this format: "May 3 2012 12:00AM"

Use the formula in cell B4 if your date TEXT does NOT include quotes
Use the formula in cell B6 if your date TEXT DOES include quotes

Col C shows the date values formatted. You could apply these formats to Col B. I just displayed them in a separate column to show the conversion from your date TEXT to date value.

Excel Workbook
	A	B	C
1		Converted
2	Your Text String	to Value	Formatted
3
4	May 3 2012 12:00AM	41032	05-03-2012
5
6	"May 3 2012 12:00AM"	41032	05-03-2012
7
8
9	------------------------------	------------------	------------------
10
Sheet2

Excel 2010

Cell Formulas
Range	Formula
B4	=VALUE(SUBSTITUTE(LEFT(A4,FIND("|",SUBSTITUTE(A4," ","|",3)))," ",", ",2))
B6	=VALUE(SUBSTITUTE(MID(A6,2,FIND("|",SUBSTITUTE(A6," ","|",3))-1)," ",", ",2))
C4	=+B4
C6	=+B6

 

[newbiexls]

Hi guys,

thanks for your fasted replys ( Specially JEFF Meyers).

i did manage to create a workaround for my needs, this is what i did:

assuming that i have the value "May 3 2012 12:00AM" without quotes on cell A1 i have created 4 more columns , B1 for Months,C1 for Days , D1 for Years and E1 for complete date.
I had also created a table with months definitions ( Jan =1 , Fev = 2 , Marc = 3....)

then i have applied the following formulas:

B1 Months -=vlookup(Left(A1,3),Months,2,0)
C1 Days - =Left(Right(A1,15),2)*1
D1 Years - =Left(Right(A1,12);4)
E1 =(C1&"-"&B1&"-"&D1)*1 to have the format 03-05-2012.

maybe this is a huge workaround but the fact is that it´s working

thank you

 

[Jeff Meyers]

Good to know that it's worked out for you. Thank you for the kind feedback.