Converting text to date

polska2180

Active Member
Joined
Oct 1, 2004
Messages
357
Office Version
  1. 365
I searched for a solution but couldn't find one. I have an output form system where the dates look like the picture attached. The data is text which I want to convert to a date but no matter what combination of formulas i find none of the get the job done. I'm not sure if its the extra spaces or what. Either way I included text to column converter image to better show how the data looks. Is there a formula that will do the job...thanks.
 

Attachments

  • Untitled.png
    Untitled.png
    21.1 KB · Views: 12

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,387
Office Version
  1. 365
Platform
  1. Windows
This may work depending on what your date system is. It presumes the month is always 3 letters.

=0+MID(SUBSTITUTE(A1," ",LEFT(A1,3)),7,11)
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,405
Office Version
  1. 365
Platform
  1. Windows
Have you tried parsing the dates with text to columns? If it doesn't work then that would suggest imported data with invalid characters that would need to be removed before the text can be converted to proper dates.
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,708
Hard to tell if additional spaces are in there.

Try:

DATE(RIGHT(D2,4),MONTH(DATEVALUE(LEFT(D2,3)&1)),NUMBERVALUE(MID(D2,5,2)))
 
Solution

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,625
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Please post an extract of your data with the forum's XL2BB.
Format the date to your preference.

try

Date and Time.xlsm
AB
12Jan 7 20212021Jan07
13Jan 28 20212021Jan28
14Nov
1b
Cell Formulas
RangeFormula
B12:B13B12=DATE(RIGHT(A12,4),MONTH(1&LEFT(A12,3)),MID(A12,5,2))
 

Watch MrExcel Video

Forum statistics

Threads
1,127,009
Messages
5,622,145
Members
415,881
Latest member
tasic89

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
Top