Convert text to date

JJones_hay

New Member
Joined
Dec 5, 2012
Messages
18
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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?
 
Upvote 0
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/190522820132/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>
 
Upvote 0
One way:
Code:
=(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
GH
92/28/2013 2:48:53 PM28/02/2013
Sheet1
 
Upvote 0
Here's an option:


Excel 2007
AB
22/28/2013 2:48:53 PM28 February 2013
31/16/2013 10:48:50 AM16 January 2013
412/17/2012 11:59:49 AM17 December 2012
Sheet1
Cell Formulas
RangeFormula
B2=TEXT(SUMPRODUCT(TRIM(MID(SUBSTITUTE(LEFT(A2,FIND(" ",A2&" ")),"/",REPT(" ",99)),{1,100,200},50))*{100,1,10000}),"0000-00-00")+0
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top