Date Changes While Concatenating

[arifaquib]

My format is changing after concatenating.

For some reason, 31/12/2021 is in "dd/mm/yyyy" and 02/01/2022 is in "mm/dd/yyyy"

=TEXT([@[Start date]], "dd/mm/yyyy")

 

[Sufiyan97]

First you need to see which month they falls by

Book1
	A	B
1	2/4/2022	2
Sheet1

Cell Formulas
Range	Formula
B1	B1	=MONTH(A1)

 

[arifaquib]

First you need to see which month they falls by

Book1
	A	B
1	2/4/2022	2
Sheet1

Cell Formulas
Range	Formula
B1	B1	=MONTH(A1)

all went 0

 

[Sufiyan97]

post some examples using below link

 

[arifaquib]

AUDIT.xlsx
	F	G	H	I
13	31/12/2021 | 18 | abdullah.ibn	31/12/2021	18:27	18
14	44593 | 8 | abdullah.ibn	02/01/2022	8:41	8
RawData

Cell Formulas
Range	Formula
F13:F14	F13	=B13& " | " &I13 & " | " &L13

 

[Alex Blakenburg]

Where did the dates column come from ?
My guess is that you got it from an external source and they are all in fact in the format dd/mm/yyyy and your Region / default format it set to mm/dd/yyyy.
Your format is accepting anything with a number in the month position < 13 and treating it as date (but with the month and day reversed) and > 13s are treated as text.

If it is a one off and you want the date in the text output to be formatted as mm/dd/yyyy then
select the data in column G
go data > text to columns
in the screen below select Date and the format in which the date came to you which I believe is dd/mm/yyyy.
Make the destination where the data is currently. (copy it back on to itself)