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.

 

[Ipsy]

It works !

I formatted the cell " jjmmaaa " and used the formula " =DATE(GAUCHE(A2;4);STXT(A2;5;2);DROITE(A2;2))"

I am very sorry about this, and a bit surprised because i didn't realise my Excel was in French, I use it quite a lot for basic formulas, I suppose because I could understand it I didn't notice which language it was in.

Thank you all again.

 

[Joe4]

I am very sorry about this, and a bit surprised because i didn't realise my Excel was in French, I use it quite a lot for basic formulas, I suppose because I could understand it I didn't notice which language it was in.

I think the error message you posted in post #3 kind of gave that away!

Anyway, glad you got it working now!

 

[Ipsy]

In my defense, so that I don't look like a complete numpty, my Excel used to accept formulas in French or English. I did have to reinstall Excel today so perhaps I didn't install it properly.
Or perhaps it's just been a long day.

Thank you all again, I'm not sure which of your replies to note as the 'solution', it took teamwork.

 

[Rick Rothstein]

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")

This would work directly on all cells in the range...

=TEXT(TEXT(A3:A4,"0000-00-00"),"ddmmyyyy")

You could affix the double negative to the beginning to form real numbers, but you would lose the leading zero for days less than 10 (unless you formatted the cell with 00000000 of course).

 

[Dave Patton]

With current version of 365, we could use the following.
• check information on TrimRange
• only this data will be in Columns A,B, and/or C
• the text or number will be 8 or 7 digits 4 for Year, 2 for Month and 1 or 2 for Day
Format the dates to your preference

T202502a.xlsm
	A	B	C	D
1	20220801	1-Aug-22	1-Aug-22
2	20240505	5-May-24	5-May-24
3	20250201	1-Feb-25	1-Feb-25
4	2025021	1-Feb-25	1-Feb-25
5	20220801	1-Aug-22	1-Aug-22	Text in A5
6	2025021	1-Feb-25	1-Feb-25	Text in A6
7	2025050505	N/A
1ee

Cell Formulas
Range	Formula
B1:B7	B1	=IFERROR(--TEXT(A.:.A,"0000-00-00"),IFERROR(--TEXT(A.:.A,"0000-00-0"),"N/A"))
C1:C7	C1	=IF(LEN(A.:.A)=8,--TEXT(A.:.A,"0000-00-00"),IF(LEN(A.:.A)=7,--TEXT(A.:.A,"0000-00-0"),""))
Dynamic array formulas.

 

[Dave Patton]

T202502a.xlsm
	A	B	C	D	E
1		ddmmyyyy
2	20220801	01082022	01082022		01082022
3	20240505	05052024	05052024		05052024
4	20250201	01022025	01022025		01022025
5	2025021	01022025	01022025		10022025
6	20220801	01082022	01082022	Text in A6	01082022
7	2025021	01022025	01022025	Text in A7	10022025
8	2025050505	N/A			05052025
9
1ee

Cell Formulas
Range	Formula
B2:B8	B2	=IFERROR(--TEXT(A2:A8,"0000-00-00"),IFERROR(--TEXT(A2:A8,"0000-00-0"),"N/A"))
C2:C8	C2	=LET(d,A2:A8,IF(LEN(d)=8,--TEXT(d,"0000-00-00"),IF(LEN(d)=7,--TEXT(d,"0000-00-0"),"")))
E2:E8	E2	=--TEXT(LEFT(A2:A8&"0",8),"0000-00-00")
Dynamic array formulas.

 

[Rick Rothstein]

With current version of 365, we could use the following.
• check information on TrimRange
• only this data will be in Columns A,B, and/or C
• the text or number will be 8 or 7 digits 4 for Year, 2 for Month and 1 or 2 for Day
Format the dates to your preference

T202502a.xlsm
	A	B	C	D
1	20220801	1-Aug-22	1-Aug-22
2	20240505	5-May-24	5-May-24
3	20250201	1-Feb-25	1-Feb-25
4	2025021	1-Feb-25	1-Feb-25
5	20220801	1-Aug-22	1-Aug-22	Text in A5
6	2025021	1-Feb-25	1-Feb-25	Text in A6
7	2025050505	N/A
1ee

Cell Formulas
Range	Formula
B1:B7	B1	=IFERROR(--TEXT(A.:.A,"0000-00-00"),IFERROR(--TEXT(A.:.A,"0000-00-0"),"N/A"))
C1:C7	C1	=IF(LEN(A.:.A)=8,--TEXT(A.:.A,"0000-00-00"),IF(LEN(A.:.A)=7,--TEXT(A.:.A,"0000-00-0"),""))
Dynamic array formulas.

Thank you for posting this... your mention of "current version" and "TrimRange" made me check and I see that I now have it in my version of Excel too! I have been waiting for the general release of this function/functionality for some time now and I am happy to see it finally has been released. I just wish Microsoft would be more proactive in letting the Excel world know when a new function or functionality has been released in general so we do not have to "trip over it by accident" or find out about it from others.

 

[Dave Patton]

Hi Rick
" have been waiting for the general release of this function/functionality for some time now and I am happy to see it finally has been released. I just wish Microsoft would be more proactive in letting the Excel world know when a new function or functionality has been released in general so we do not have to "trip over it by accident" or find out about it from others."

I agree 100%; communication could be much better.