Convert text to date

[JJones_hay]

Guys, I have some horrible extract data with differing date formats that I need to convert to something consistent that I can format as a date.

This is an example of data.... all with general format at the moment.

2/28/2013 2:48:53 PM

1/16/2013 10:48:50 AM

12/17/2012 11:59:49 AM

<tbody>
</tbody>

I have used this formula to extract the date portion, but I can't get this to then format as date. How do I convert this to the julian date, so I can then apply a date format?

=LEFT(G9,SEARCH("/",G9)+7)

(The day portion of this date always has a leading zero)

Thanks

 

[phil152003]

Try this:

=DATE(YEAR(G9),MONTH(G9),DAY(G9))

 

[Firefly2012]

Hi

Modify your original formula to:

=LEFT(G9,SEARCH("/",G9)+7)+0

and format this cell as a date. I am assuming that you have your regional settings for date as mm/dd/yyyy or equivalent?

 

[JJones_hay]

Hi

Modify your original formula to:

=LEFT(G9,SEARCH("/",G9)+7)+0

and format this cell as a date. I am assuming that you have your regional settings for date as mm/dd/yyyy or equivalent?

I get a #value using that FF. Regional settings will be dd/mm/yyyy for UK.


I'm trying to build it up like this, but the year is giving me issues....

28/01/1905	2	28	2013	2/28/2013 2:48:53 PM
=DATE(YEAR(F10),MONTH(D10),DAY(E10))	=RIGHT(LEFT(G10,SEARCH("/",G10)-1),4)	=RIGHT(LEFT(G10,SEARCH("/",G10)+2),2)	=RIGHT(LEFT(G10,SEARCH("/",G10)+7),4)	1/16/2013 10:48:50 AM

<tbody>
</tbody>

<tbody>
</tbody>

 

[FormR]

ADVERTISEMENT

One way:

=(MID(G9,FIND("/",G9)+1,2)&"/"&LEFT(G9,FIND("/",G9)-1)&"/"&MID(G9,FIND(" ",G9)-4,4))+0

Format the cell containing the formula as a date.

Excel Workbook
	G	H
9	2/28/2013 2:48:53 PM	28/02/2013
Sheet1

 

[Firefly2012]

Here's an option:

Excel 2007
	A	B
2	2/28/2013 2:48:53 PM	28 February 2013
3	1/16/2013 10:48:50 AM	16 January 2013
4	12/17/2012 11:59:49 AM	17 December 2012
Sheet1

Cell Formulas
Range	Formula
B2	=TEXT(SUMPRODUCT(TRIM(MID(SUBSTITUTE(LEFT(A2,FIND(" ",A2&" ")),"/",REPT(" ",99)),{1,100,200},50))*{100,1,10000}),"0000-00-00")+0

 

[JJones_hay]

ADVERTISEMENT

One way:

=(MID(G9,FIND("/",G9)+1,2)&"/"&LEFT(G9,FIND("/",G9)-1)&"/"&MID(G9,FIND(" ",G9)-4,4))+0

Format the cell containing the formula as a date.

Excel tables to the web >> Excel Jeanie HTML 4

Thank you, works great.
Thanks too FF

 

[Firefly2012]

I would note that FormR's formula will need amendment if you have dates with only a single day value eg:

1/6/2013 10:48:50 AM

 

[FormR]

True, but from the OP.

(The day portion of this date always has a leading zero)

 

[Firefly2012]

Ah good point - I had missed that