Convert Format Date From Mm/Dd/Yyyy to Dd/Mm/Yyyy

[muhammad susanto]

hi all

how to convert format date from mm/dd/yyyy change to dd/mm/yyyy
this my sample

checking date lhppu.xlsx
	D	E
1	original date	convert date
2	02/02/2023	02/02/2023
3	03/02/2023	02/03/2023
4	04/03/2023	03/04/2023
5	05/03/2023	03/05/2023
6	06/05/2023	05/06/2023
7	07/04/2023	04/07/2023
8	08/01/2023	01/08/2023
9	09/04/2023	04/09/2023
10	10/02/2023	02/10/2023
11	11/01/2023	01/11/2023
Sheet1

i have change into Regional language but not work
i hope someone give me solultion
please, do not use VBA

susant

 

[Alex Blakenburg]

I am pretty sure you do have LET, so try this:
PS: If you have to resort to a screenshot please include the Row & Column references, it creates less work for you in having to do a conversion and less chance of typing errors,
Note: the text format choice might seem counterintuitive but try it anyway

Also if it is a one off conversion Text To Columns and in the Data format section put Date and MDY might be quicker

20231216 US to UK Date format muhammad susanto.xlsx
	D	E
1	original date	convert date
2	12/16/2023	16/12/2023
3	10/30/2023	30/10/2023
4	12/02/2022	2/12/2022
5	3/02/2023	2/03/2023
Main

Cell Formulas
Range	Formula
E2:E5	E2	=LET(sDt,TEXT($D2,"dd/mm/yyyy"), DATE(RIGHT(sDt,4),LEFT(sDt,2),MID(sDt,4,2)))

 

[Tetra201]

Why not use the "Text to Columns" wizard and convert the entire column at once?

 

[muhammad susanto]

I am pretty sure you do have LET, so try this:
PS: If you have to resort to a screenshot please include the Row & Column references, it creates less work for you in having to do a conversion and less chance of typing errors,
Note: the text format choice might seem counterintuitive but try it anyway

Also if it is a one off conversion Text To Columns and in the Data format section put Date and MDY might be quicker

20231216 US to UK Date format muhammad susanto.xlsx
	D	E
1	original date	convert date
2	12/16/2023	16/12/2023
3	10/30/2023	30/10/2023
4	12/02/2022	2/12/2022
5	3/02/2023	2/03/2023
Main

Cell Formulas
Range	Formula
E2:E5	E2	=LET(sDt,TEXT($D2,"dd/mm/yyyy"), DATE(RIGHT(sDt,4),LEFT(sDt,2),MID(sDt,4,2)))

hi, Alex Blakenburg,,

thank you so much. Working well perfecto!!!

​

 

[muhammad susanto]

Why not use the "Text to Columns" wizard and convert the entire column at once?

hi Tetra, sorry i'm not usually using Text to Columns also a reason certainty.

 

[Alex Blakenburg]

hi, Alex Blakenburg,,

thank you so much. Working well perfecto!!!

​

Thanks for letting us know. Glad we could help.

 

[nisanthp]

I am pretty sure you do have LET, so try this:
PS: If you have to resort to a screenshot please include the Row & Column references, it creates less work for you in having to do a conversion and less chance of typing errors,
Note: the text format choice might seem counterintuitive but try it anyway

Also if it is a one off conversion Text To Columns and in the Data format section put Date and MDY might be quicker

Thank you, I was also looking for a solution and found this.