Formula to get all dates in dd-mm-yyyy

[RAJESH1960]

Hello formula experts
I have a data received in which the date format is like in the column A. I want to get the all the dates correct in the format dd-mm-yyyy in the adjacent column with the help of a formula. I tried the value formula but half the dates are showing value error because of the different formats of the dates. Half of the dates which are showing error, the year can't be viewed in the formula bar. I need your expert advise to get the dates.

Get date format.xlsx
	A	B	C
1	Date	Correct Date Format	Expected Result
2	9 Apr 2020	09-04-2020	09-04-2020
3	9 Apr 2020	09-04-2020	09-04-2020
4	9 Apr 2020	09-04-2020	09-04-2020
5	10 Apr 2020	#VALUE!	10-04-2020
6	10 Apr 2020	#VALUE!	10-04-2020
7	10 Apr 2020	#VALUE!	10-04-2020
8	10 Apr 2020	#VALUE!	10-04-2020
9	10 Apr 2020	#VALUE!	10-04-2020
10	31 Mar 2021	#VALUE!	31-03-2021
11	31 Mar 2021	#VALUE!	31-03-2021
12	31 Mar 2021	#VALUE!	31-03-2021
Get date format

Cell Formulas
Range	Formula
B2:B12	B2	=VALUE(A2)

 

[Peter_SSs]

Try this formula and format the column as "dd-mm-yyyy"

22 06 28.xlsm
	A	B
1	Date	Correct Date Format
2	9 Apr 2020	09-04-2020
3	9 Apr 2020	09-04-2020
4	9 Apr 2020	09-04-2020
5	10 Apr 2020	10-04-2020
6	10 Apr 2020	10-04-2020
7	10 Apr 2020	10-04-2020
8	10 Apr 2020	10-04-2020
9	10 Apr 2020	10-04-2020
10	31 Mar 2021	31-03-2021
11	31 Mar 2021	31-03-2021
12	31 Mar 2021	31-03-2021
Dates

Cell Formulas
Range	Formula
B2:B12	B2	=SUBSTITUTE(A2,CHAR(10)," ")+0

 

[RAJESH1960]

Wow! solved in one shot. Thanks Peter SSs.
Can you please tell me how did it take the year 2020 and 2021 and from where...?
You can't view the year in the formula bar but can be viewed in the cell.

 

[Peter_SSs]

You can't view the year in the formula bar

You can if you
- expand the formula bar like below

or if your formula bar is only one row high, click in it and then press the down arrow

-

 

[RAJESH1960]

You can if you
- expand the formula bar like below
View attachment 68104

or if your formula bar is only one row high, click in it and then press the down arrow

View attachment 68105
-

Ah. Yes I noticed it now after you told me. If I want to view all the dates with one extra character then I have to expand the formula bar. But what I couldn't understand is that if the whole date is entered in the cell, why is it not taking the value of the date by the value formula.

 

[Peter_SSs]

why is it not taking the value of the date by the value formula.

Because Excel does not expect a line-feed character between month and year (or between day and month)

 

[RAJESH1960]

I thought maybe they have pressed Alt+ Enter while entering the date

 

[RAJESH1960]

Anyways, Thanks once again Peter SSs.

 

[Peter_SSs]

I thought maybe they have pressed Alt+ Enter while entering the date

Yes, that is correct if the dates were manually entered. Just the same, Alt+Enter is not acceptable as a day/month/year separator in Excel.