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

 

[Dave Patton]

1.
Your Regional Settings for Date are dd-mm-yy

Dates Time.xlsb
	A	B	C	D
1	original date	convert date
2	02-Feb-23	02-Feb-23		02-Feb-23
3	03-Feb-23	02-Mar-23		02-Mar-23
4	04-Mar-23	03-Apr-23		03-Apr-23
5	05-Mar-23	03-May-23		03-May-23
6	06-May-23	05-Jun-23		05-Jun-23
7	07-Apr-23	04-Jul-23		04-Jul-23
8	08-Jan-23	01-Aug-23		01-Aug-23
9	09-Apr-23	04-Sep-23		04-Sep-23
10	10-Feb-23	02-Oct-23		02-Oct-23
11	11-Jan-23	01-Nov-23		01-Nov-23
5a

Cell Formulas
Range	Formula
D2:D11	D2	=DATE(YEAR(A2),DAY(A2),MONTH(A2))

 

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

 

[muhammad susanto]

hi Dave Patton,

thank you so much. working well.

 

[muhammad susanto]

hi Dave..sorry
for this date your formula not work

16/12/2022 --- the result is 12/04/2023
27/12/2022 --- 12/03/2024

 

[Alex Blakenburg]

I noticed you were missing dates with days >12 from your sample data.
1) what is your region date setting format, is it dd/mm/yyyy ?
ie if on new sheet in a cell you use Ctr+; (semi=colon) does it show 16/12/2023 ?

2) you said column D was in the format mm/dd/yyyy but if column D is showing 16/12/2022 & 27/12/2022 then it unlikely that it is using mm/dd/yyyy format
Can you check your dates back to the source and confirm that they are showing as mm/dd/yyyy ? (look for dates with days > 12 to be sure.)

 

[muhammad susanto]

hello Alex

for my region date setting format is in dd/mm/yyyy and use semi colon
here new sample

original date	convert date
16/12/2023	12/04/2024
10/30/2023	#VALUE!
12/02/2022	02/12/2022
03/02/2023	02/03/2023

here the formula that i use

=DATE(YEAR(D2);DAY(D2);MONTH(D2))

how to fix this problem

 

[Alex Blakenburg]

Is your original data really untouched ?
16/12/2023 is dd/mm/yyyy while 10/30/2023 is mm/dd/yyyy
That seems unlikely.

Where is the data coming from and how are you getting it into excel ?

 

[muhammad susanto]

ups...sorry Alex, i think i confused..
here the new data correct

original date	convert date
12/16/2023	#VALUE!
10/30/2023	#VALUE!
12/02/2022	02/12/2022
03/02/2023	02/03/2023

 

[Dave Patton]

Dates Time.xlsb
	C	D	E
1		original date	convert date
2	Text	12/16/2023	16-Dec-23
3	Text	10/30/2023	30-Oct-23
4	Date	12-02-22	02-Dec-22
5	Date	03-02-23	02-Mar-23
6
5a

Cell Formulas
Range	Formula
E2:E3	E2	=--TEXTJOIN("-",1,INDEX(TEXTSPLIT(D2,"/",,1),{3,1,2}))
E4:E5	E4	=DATE(YEAR(D4),DAY(D4),MONTH(D4))