CSV - Converting General Text to Dates

mdqs

New Member
Joined
Oct 18, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have a small CSV in which the information in the first column (dates) imports as, e.g., Thu Jul 01 2021 in 'General' number format. I'd like to convert information in this column to dates of any recognizable style (short, long, does not matter). Changing number format does not alter the text whatsoever.

I've also tried text-to-columns and deliminating by spaces, then concatenating to create dates. No luck.

Any help is appreciated! Sorry if this has been asked before.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,174
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
If you are sure that the text dates will always be of this form: "Thu Jul 01 2021", then you can use a formula in a new column to convert the text to a excel date. One example.

Excel Formula:
=DATEVALUE(SUBSTITUTE(MONTH(DATEVALUE(MID(A1,5,3)&1))& MID(A1,8,99)," ","-"))

If the text date format varies, then it becomes harder.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
5,062
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Ensure that there are no extraneous text such as blanks before or after the date

Date and Time 4.xlsm
AB
1
2Thu Jul 01 20211-Jul-21
3
1f
Cell Formulas
RangeFormula
B2B2=DATE(RIGHT(A2,4),MONTH(MID(A2,5,3)&1),MID(A2,9,2))
 

mdqs

New Member
Joined
Oct 18, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Beautiful. Thank you both so much. Challenge solved.
 

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
791
Office Version
  1. 365
Platform
  1. Windows
Excel Formula:
=DATEVALUE(MID(A1,FIND(" ",A1)+1,LEN(A1)))
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,578
Messages
5,765,200
Members
425,267
Latest member
bishopc22

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