Change Multiple Date formats to single date format

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
Hello Good Evening, This is the kind of data I receive from a client. The dates are written in different formats. I tried converting the text in the adjoining column by using the formula =text(A1,"dd-mm-yyyy") but I was not able to convert the whole range. Please suggest any other formula if any, that I can apply .?
Book2
AB
110-04-201910-04-2019
210-04-201910-04-2019
330-04-201930-04-2019
4 21.05.2019 21.05.2019
5 21.05.2019 21.05.2019
6 30.05.2019 30.05.2019
7 17.05.2019 17.05.2019
8 23.05.2019 23.05.2019
9 23.05.2019 23.05.2019
10 22.05.2019 22.05.2019
11 31.05.2019 31.05.2019
12 09.05.2019 09.05.2019
13 08.05.2019 08.05.2019
14 02.05.2019 02.05.2019
15 06.05.2019 06.05.2019
16 27.05.2019 27.05.2019
17 23.05.2019 23.05.2019
18 22.05.2019 22.05.2019
19 24.05.2019 24.05.2019
20 25.05.2019 25.05.2019
21 07.05.2019 07.05.2019
22 29.05.2019 29.05.2019
23 31.05.2019 31.05.2019
24 22.05.2019 22.05.2019
25 01.06.2019 01.06.2019
26 08.06.2019 08.06.2019
27 06.06.2019 06.06.2019
28 10.06.2019 10.06.2019
29 12.06.2019 12.06.2019
30 08.06.2019 08.06.2019
31 14.06.2019 14.06.2019
32 19.06.2019 19.06.2019
33 17.06.2019 17.06.2019
34 24.06.2019 24.06.2019
35 20.06.2019 20.06.2019
36 25.06.2019 25.06.2019
37 22.06.2019 22.06.2019
38 26.06.2019 26.06.2019
39 21.06.2019 21.06.2019
40 26.07.2019 26.07.2019
41 05.07.2019 05.07.2019
42 05.07.2019 05.07.2019
43 08.07.2019 08.07.2019
44 08.07.2019 08.07.2019
45 06.07.2019 06.07.2019
46 12.07.2019 12.07.2019
47 08.07.2019 08.07.2019
48 09.07.2019 09.07.2019
49 10.07.2019 10.07.2019
50 18.07.2019 18.07.2019
51 18.07.2019 18.07.2019
52 18.07.2019 18.07.2019
53 24.07.2019 24.07.2019
54 30.07.2019 30.07.2019
55 24.07.2019 24.07.2019
56 26.07.2019 26.07.2019
57 27.07.2019 27.07.2019
58 31.07.2019 31.07.2019
59 31.07.2019 31.07.2019
6001-08-201901-08-2019
6107-08-201907-08-2019
626.8.20196.8.2019
6302-08-201902-08-2019
6412-08-201912-08-2019
6514-08-201914-08-2019
6616-08-201916-08-2019
6719-08-201919-08-2019
6826-08-201926-08-2019
6930.8.201930.8.2019
7029-08-201929-08-2019
7129-08-201929-08-2019
7229-08-201929-08-2019
7319.8.201919.8.2019
746.8.20196.8.2019
7531.8.201931.8.2019
7631.8.201931.8.2019
7716.8.201916.8.2019
786.8.20196.8.2019
7919.8.201919.8.2019
8031.8.201931.8.2019
8110.09.201910.09.2019
8210.09.201910.09.2019
8310.09.201910.09.2019
8411.09.201911.09.2019
8526.09.201926.09.2019
8603.09.201903.09.2019
8710.09.201910.09.2019
8811.09.201911.09.2019
8923.09.201923.09.2019
9026.09.201926.09.2019
9128.09.201928.09.2019
9230.09.201930.09.2019
9312.09.201912.09.2019
9409.09.201909.09.2019
9505.09.201905.09.2019
9623.09.201923.09.2019
9707.09.201907.09.2019
9818.09.201918.09.2019
9916.09.201916.09.2019
10021.09.201921.09.2019
10110.09.201910.09.2019
10210.09.201910.09.2019
10316.09.201916.09.2019
10406.09.201906.09.2019
10521-10-201921-10-2019
10610-10-201910-10-2019
10716-10-201916-10-2019
10831-10-201931-10-2019
10930-10-201930-10-2019
11014-10-201914-10-2019
11116-10-201916-10-2019
11216-10-201916-10-2019
11317-10-201917-10-2019
11401-10-201901-10-2019
11526-10-201926-10-2019
11631-10-201931-10-2019
11708-10-201908-10-2019
11802-10-201902-10-2019
11917-10-201917-10-2019
12009-10-201909-10-2019
12110-10-201910-10-2019
12209-10-201909-10-2019
12324-10-201924-10-2019
12411-10-201911-10-2019
12521-10-201921-10-2019
12618-10-201918-10-2019
12731-10-201931-10-2019
12816-10-201916-10-2019
12902-11-201902-11-2019
13006-11-201906-11-2019
13114-11-201914-11-2019
132 30/11/2019 30/11/2019
13319-11-201919-11-2019
13419-11-201919-11-2019
13519-11-201919-11-2019
13619-11-201919-11-2019
13719/11.201919/11.2019
13802-11-201902-11-2019
13919-11-201919-11-2019
14020-11-201920-11-2019
14130-11-201930-11-2019
14201-11-201901-11-2019
14306-11-201906-11-2019
14411-11-201911-11-2019
14516-11-201916-11-2019
14601.11.201901.11.2019
14722.11.201922.11.2019
14822.11.201922.11.2019
14917.11.201917.11.2019
15030.11.201930.11.2019
15117.11.201917.11.2019
15221.11.201921.11.2019
15323.11.201923.11.2019
15421.11.201921.11.2019
15518.11.201918.11.2019
15601.11.201901.11.2019
15718-12-201918-12-2019
15828-12-201928-12-2019
15928-12-201928-12-2019
16031-12-201931-12-2019
16103-12-201903-12-2019
16209-12-201909-12-2019
16312-12-201912-12-2019
16423-12-201923-12-2019
16514-12-201914-12-2019
16616-12-201916-12-2019
16726-12-201926-12-2019
16826-12-201926-12-2019
16928-12-201928-12-2019
17002-12-201902-12-2019
17124-12-201924-12-2019
17227-12-201927-12-2019
17313-12-201913-12-2019
17415-12-201915-12-2019
17515-12-201915-12-2019
17631-12-201931-12-2019
17718-12-201918-12-2019
17826.12.201926.12.2019
17919.12.201919.12.2019
18031.12.201931.12.2019
18117.12.201917.12.2019
18215.12.201915.12.2019
18301.12.201901.12.2019
18402.12.201902.12.2019
18503.12.201903.12.2019
18608-01-202008-01-2020
18721-01-202021-01-2020
18821-01-202021-01-2020
18931-01-202031-01-2020
19030-01-202030-01-2020
19116-01-202016-01-2020
19207-01-202007-01-2020
19310-01-202010-01-2020
19428-01-202028-01-2020
19525-01-202025-01-2020
19620-01-202020-01-2020
19720-01-202020-01-2020
19813-01-202013-01-2020
19921-01-202021-01-2020
20031-01-202031-01-2020
20122-01-202022-01-2020
20227-01-202027-01-2020
20327.01.2027.01.20
20428.01.2028.01.20
20518.01.2018.01.20
20619.01.2019.01.20
20715.01.2015.01.20
20831.01.202031.01.2020
20930.01.2030.01.20
21028.01.2028.01.20
21118.01.2018.01.20
21217-02-202017-02-2020
21321-02-202021-02-2020
21429-02-202029-02-2020
21524-02-202024-02-2020
21624-02-202024-02-2020
21724-02-202024-02-2020
21824.2.202024.2.2020
21903-02-202003-02-2020
22003-02-202003-02-2020
22111-02-202011-02-2020
22217-02-202017-02-2020
22324-02-202024-02-2020
22429.02.202029.02.2020
22501-02-202001-02-2020
22607-02-202007-02-2020
22711-02-202011-02-2020
22820-02-202020-02-2020
22929-02-202029-02-2020
23028.2.202028.2.2020
23125.2.2025.2.20
23215-02-202015-02-2020
23312-02-202012-02-2020
23425-02-202025-02-2020
23517.02.202017.02.2020
23611.02.202011.02.2020
23715.02.202015.02.2020
23810.02.202010.02.2020
23913.2.202013.2.2020
24015.2.202015.2.2020
24114.2.2014.2.20
2425.2.20205.2.2020
2438.2.20208.2.2020
2448.3.20208.3.2020
24511.3.202011.3.2020
24631.3.202031.3.2020
24731.3.202031.3.2020
24816.3.202016.3.2020
24917.3.202017.3.2020
2506.3.20206.3.2020
25103-03-202003-03-2020
25211.3.202011.3.2020
25311.3.202011.3.2020
25414.3.202014.3.2020
25516.3.202016.3.2020
25611.3.202011.3.2020
25731.3.202031.3.2020
2583.3.20203.3.2020
25929.3.202029.3.2020
26019.3.202019.3.2020
2615.3.20205.3.2020
26220.3.202020.3.2020
2636.3.20206.3.2020
2647.3.20207.3.2020
2656.3.20206.3.2020
2667.3.20207.3.2020
26716.3.202016.3.2020
26818.3.202018.3.2020
Sheet1
Cell Formulas
RangeFormula
B1:B268B1=TEXT(A1,"dd-mm-yyyy")
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
How about
Excel Formula:
=SUBSTITUTE(A1,".","/")+0
The month and year are changed after applying the above formula. Sorry wrong reference given.
 
Upvote 0
You're welcome & thanks for the feedback.
When "Marking as Solution" please ensure that you mark the post the helped you and NOT your post saying it worked.
 
Upvote 0
Are you saying that the formula I suggested worked, or something else?
Also please stop making duplicate posts that say the same thing.
 
Upvote 0
Are you saying that the formula I suggested worked, or something else?
Also please stop making duplicate posts that say the same thing.
Fluff. Your formula is correct. I replied to the wrong post by mistake. So changed and marked the right post as solution.
 
Upvote 0
You have once again marked YOUR post as the solution, you should mark the HELPERS post that helped you. In this case post#2
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top