Formatting Text Data to Proper Date Format

[claybwagner28]

Hello, I downlaoded a source file which the dates are erronieuns typed in as text.
For example, Cell B4 has the following cell entry “Jan 7 2022” with the extra space, no comma and listed as general in format. Ultimately I am trying to change “Jan 7 2022” to “1/7/22”.
I tied to use TRIM but the file still reads it as text.

Any sugesstions? Thanks in advance!
Clay

 

[Fluff]

If you use MDY format try

=TRIM(B4)+0

 

[claybwagner28]

If you use MDY format try

=TRIM(B4)+0

Thanks for the reply. I tried that and it gives me a #VALUE error. I may try to use CLEAN or copy and paste but there are thousnads of records.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[claybwagner28]

Will update my account details. The subscription says Version 2112.

I ran =ISTEXT(B4) and it comes back true.

 

[Fluff]

2112 is the build number, rather than the version, if you look at your account it should tell you the version towards the top

 

[claybwagner28]

Microsoft 365 E3. Thanks

 

[Fluff]

Ok if you put this formula in another cell it will spill down & should look like this

+Fluff 1.xlsm
	A	B	C	D
1
2
3
4		Jan 7 2022		74
5				97
6				110
7				32
8				32
9				55
10				32
11				50
12				48
13				50
14				50
Main

Cell Formulas
Range	Formula
D4:D14	D4	=CODE(MID(B4,SEQUENCE(LEN(B4)),1))
Dynamic array formulas.

What values do you get, especially the 4th & 5th values

 

[eastrand]

Probably not the best way but if the Date value is text the below formula should work.

=DATE(RIGHT(TRIM(B4),4),MONTH(DATEVALUE(LEFT(TRIM(B4),3)&" 1, 2022")),MID(TRIM(B4),5,1))

 

[claybwagner28]

Almost there. It cuts off the day for 2 digits. For example “Dec 30 2021” returns 12/3/21. Gotta love governement provided data (Covid Cases)