Convert numbers to date 20220801 -> 01082022

[Ipsy]

Hello,

I have had a look through the forum but wasn't able to find an answer that worked for me, possible I didn't understand it either, I did end up with a lot of hashtags in my cell !

I am looking for a nice and easy way to convert a column of numbers from one format to another :

For example, I have 20220801, which is the way the date is presented in my file, but I need it to read 01082022 or 010822

Any help would be greatly appreciated.

 

[DanteAmor]

Try

Libro1
	A	B
1
2	20220801	01/08/2022
Hoja1

Cell Formulas
Range	Formula
B2	B2	=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))

Use cell format to view:

Libro1
	A	B
1
2	20220801	01082022
Hoja1

Cell Formulas
Range	Formula
B2	B2	=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))

 

[Ipsy]

Thank you for your reply.

I copied and pasted your formula and received the particularly unhelpful and dreaded message below :
Both columns A and B are formatted "Standard" is there something else I should be doing?

 

[Joe4]

Welcome to the Board!

Does your version of Excel perhaps using semi-colons instead of periods as argument separators?
If so, you will need to replace them in the formula DanteAmor gave you, i.e.

=DATE(LEFT(A2;4),MID(A2;5;2),RIGHT(A2;2))

 

[Ipsy]

Hello there,

I tried your formula with the semi-colons and whilst I didn't get the dreaded message the cell just takes it as text.

 

[RoryA]

Make sure the cell is not formatted as Text before you enter the formula. Also, Joe missed a couple of commas:

=DATE(LEFT(A2;4);MID(A2;5;2);RIGHT(A2;2))

 

[Joe4]

That means that the cell you placed it in is pre-formatted as Text!
Change the cell format to your desired date format, and re-enter your formula, noting Rory's edits.

(Thanks for the catch, Rory!)

 

[DanteAmor]

Try with semicolon:

=DATE(LEFT(A2;4);MID(A2;5;2);RIGHT(A2;2))

Change the cell format to custom use ddmmyyyy

 

[Fluff]

It looks as though you are using a French version of Excel, so try

=DATE(GAUCHE(A2;4);STXT(A2;5;2);DROITE(A2;2))

 

[Dave Patton]

N.B. My system's Regional Settings include short date dd-mmm-yy

T202502a.xlsm
	A	B	C	D
1	Data Format	dd-mmm-yy	General	yyyymmdd
2
3	20220801	1-Aug-22	44774	20220801
4	20220801	1-Aug-22	44774	20220801
5
1e

Cell Formulas
Range	Formula
B3	B3	=--TEXT(A3,"0000-00-00")
C3	C3	=--TEXT(A3,"0000-00-00")
D3	D3	=--TEXT(A3,"0000-00-00")
B4	B4	=--TEXT(LEFT(A4,8),"0000-00-00")
C4	C4	=--TEXT(LEFT(A4,8),"0000-00-00")
D4	D4	=--TEXT(LEFT(A4,8),"0000-00-00")