Text & General Format to Data Format all the data - code/VBA required

[Vishaal]

Hi all,

we have the following data and want to convert in "date", we have tried
Code 1 =TEXT(A1,"mm\/dd\/yyyy")+0
Code 2 =TEXT(G12,"mm/dd/yyyy")

but problem not solved, pls check the result after using the above code and provide the code/vba, also when we copy the data after using code 1, we cant get the value as data

Data

Excel 2010 32 bit

	A​	B​	C​
1​	its my format which I want to change in date format	​	​
2​	Date​	​	​
3​	01-04-2019​	=TEXT(A3,"mm\/dd\/yyyy")+0​	=TEXT(A3,"mm/dd/yyyy")​
4​	02-04-2019​	=TEXT(A4,"mm\/dd\/yyyy")+0​	=TEXT(A4,"mm/dd/yyyy")​
5​	02-04-2019​	=TEXT(A5,"mm\/dd\/yyyy")+0​	=TEXT(A5,"mm/dd/yyyy")​
6​	04-04-2019​	=TEXT(A6,"mm\/dd\/yyyy")+0​	=TEXT(A6,"mm/dd/yyyy")​
7​	05-04-2019​	=TEXT(A7,"mm\/dd\/yyyy")+0​	=TEXT(A7,"mm/dd/yyyy")​
8​	06-04-2019​	=TEXT(A8,"mm\/dd\/yyyy")+0​	=TEXT(A8,"mm/dd/yyyy")​
9​	24-09-2019​	=TEXT(A9,"mm\/dd\/yyyy")+0​	=TEXT(A9,"mm/dd/yyyy")​
10​	4/5/2019​	=TEXT(A10,"mm\/dd\/yyyy")+0​	=TEXT(A10,"mm/dd/yyyy")​
11​	4/9/2019​	=TEXT(A11,"mm\/dd\/yyyy")+0​	=TEXT(A11,"mm/dd/yyyy")​
12​	4/17/2019​	=TEXT(A12,"mm\/dd\/yyyy")+0​	=TEXT(A12,"mm/dd/yyyy")​
13​	4/26/2019​	=TEXT(A13,"mm\/dd\/yyyy")+0​	=TEXT(A13,"mm/dd/yyyy")​
14​	5/4/2019​	=TEXT(A14,"mm\/dd\/yyyy")+0​	=TEXT(A14,"mm/dd/yyyy")​
15​	5/4/2019​	=TEXT(A15,"mm\/dd\/yyyy")+0​	=TEXT(A15,"mm/dd/yyyy")​
16​	5/21/2019​	=TEXT(A16,"mm\/dd\/yyyy")+0​	=TEXT(A16,"mm/dd/yyyy")​
17​	20-07-2019​	=TEXT(A17,"mm\/dd\/yyyy")+0​	=TEXT(A17,"mm/dd/yyyy")​
18​	06/08/19​	=TEXT(A18,"mm\/dd\/yyyy")+0​	=TEXT(A18,"mm/dd/yyyy")​
19​	24-07-2019​	=TEXT(A19,"mm\/dd\/yyyy")+0​	=TEXT(A19,"mm/dd/yyyy")​
20​	26/09/2019​	=TEXT(A20,"mm\/dd\/yyyy")+0​	=TEXT(A20,"mm/dd/yyyy")​

Sheet: Sheet1

Result Data
Excel 2010 32 bit

	A​	B​	C​
1​	its my format which I want to change in date format	​	​
2​	Date​	​	​
3​	01-04-2019​	01-04-2019​	04/01/2019​
4​	02-04-2019​	02-04-2019​	04/02/2019​
5​	02-04-2019​	02-04-2019​	04/02/2019​
6​	04-04-2019​	04-04-2019​	04/04/2019​
7​	05-04-2019​	05-04-2019​	04/05/2019​
8​	06-04-2019​	06-04-2019​	04/06/2019​
9​	24-09-2019​	24-09-2019​	09/24/2019​
10​	4/5/2019​	05-04-2019​	04/05/2019​
11​	4/9/2019​	09-04-2019​	04/09/2019​
12​	4/17/2019​	17-04-2019​	04/17/2019​
13​	4/26/2019​	26-04-2019​	04/26/2019​
14​	5/4/2019​	04-05-2019​	05/04/2019​
15​	5/4/2019​	04-05-2019​	05/04/2019​
16​	5/21/2019​	21-05-2019​	05/21/2019​
17​	20-07-2019​	#VALUE!​	20-07-2019​
18​	06/08/19​	08-06-2019​	06/08/2019​
19​	24-07-2019​	#VALUE!​	24-07-2019​
20​	26/09/2019​	26-09-2019​	09/26/2019​

Sheet: Sheet1

 

[footoo]

=date(right(a3,4),mid(a3,4,2),left(a3,2))

 

[Vishaal]

No, its not working, after using to above code getting the below result

9/12/3560

 

[footoo]

Is column A formatted as text?

 

[footoo]

If you have a mixture of dates and text in column A :

=IF(ISNUMBER(A3),A3,DATE(RIGHT(A3,4),MID(A3,4,2),LEFT(A3,2)))

 

[Vishaal]

we have used this code, its fine but when we use filter for sort the month
there is problem
Its Showing February and March Month but we have not any entry on that particular month...

Pls check the #1
Result Data
A19 and A20 value after formula C19 and C20

pls check the below sheet with your formula

Excel 2010 32 bit

	A​	B​
1​	its my format which I want to change in date format	​
2​	Date​	​
3​	1/4/2019​	1/4/2019​
4​	2/4/2019​	2/4/2019​
5​	2/4/2019​	2/4/2019​
6​	4/4/2019​	4/4/2019​
7​	5/4/2019​	5/4/2019​
8​	6/4/2019​	6/4/2019​
9​	24-09-2019​	9/24/2019​
10​	4/5/2019​	4/5/2019​
11​	4/9/2019​	4/9/2019​
12​	4/17/2019​	4/17/2019​
13​	4/26/2019​	4/26/2019​
14​	5/4/2019​	5/4/2019​
15​	5/4/2019​	5/4/2019​
16​	5/21/2019​	5/21/2019​
17​	20-07-2019​	7/20/2019​
18​	6/8/2019​	6/8/2019​
19​	24-07-2019​	7/24/2019​
20​	26/09/2019​	9/26/2019​

Sheet: Sheet1

with code
Excel 2010 32 bit

	A​	B​
1​	its my format which I want to change in date format	​
2​	Date​	​
3​	1/4/2019​	=IF(ISNUMBER(A3),A3,DATE(RIGHT(A3,4),MID(A3,4,2),LEFT(A3,2)))​
4​	2/4/2019​	=IF(ISNUMBER(A4),A4,DATE(RIGHT(A4,4),MID(A4,4,2),LEFT(A4,2)))​
5​	2/4/2019​	=IF(ISNUMBER(A5),A5,DATE(RIGHT(A5,4),MID(A5,4,2),LEFT(A5,2)))​
6​	4/4/2019​	=IF(ISNUMBER(A6),A6,DATE(RIGHT(A6,4),MID(A6,4,2),LEFT(A6,2)))​
7​	5/4/2019​	=IF(ISNUMBER(A7),A7,DATE(RIGHT(A7,4),MID(A7,4,2),LEFT(A7,2)))​
8​	6/4/2019​	=IF(ISNUMBER(A8),A8,DATE(RIGHT(A8,4),MID(A8,4,2),LEFT(A8,2)))​
9​	24-09-2019​	=IF(ISNUMBER(A9),A9,DATE(RIGHT(A9,4),MID(A9,4,2),LEFT(A9,2)))​
10​	4/5/2019​	=IF(ISNUMBER(A10),A10,DATE(RIGHT(A10,4),MID(A10,4,2),LEFT(A10,2)))​
11​	4/9/2019​	=IF(ISNUMBER(A11),A11,DATE(RIGHT(A11,4),MID(A11,4,2),LEFT(A11,2)))​
12​	4/17/2019​	=IF(ISNUMBER(A12),A12,DATE(RIGHT(A12,4),MID(A12,4,2),LEFT(A12,2)))​
13​	4/26/2019​	=IF(ISNUMBER(A13),A13,DATE(RIGHT(A13,4),MID(A13,4,2),LEFT(A13,2)))​
14​	5/4/2019​	=IF(ISNUMBER(A14),A14,DATE(RIGHT(A14,4),MID(A14,4,2),LEFT(A14,2)))​
15​	5/4/2019​	=IF(ISNUMBER(A15),A15,DATE(RIGHT(A15,4),MID(A15,4,2),LEFT(A15,2)))​
16​	5/21/2019​	=IF(ISNUMBER(A16),A16,DATE(RIGHT(A16,4),MID(A16,4,2),LEFT(A16,2)))​
17​	20-07-2019​	=IF(ISNUMBER(A17),A17,DATE(RIGHT(A17,4),MID(A17,4,2),LEFT(A17,2)))​
18​	6/8/2019​	=IF(ISNUMBER(A18),A18,DATE(RIGHT(A18,4),MID(A18,4,2),LEFT(A18,2)))​
19​	24-07-2019​	=IF(ISNUMBER(A19),A19,DATE(RIGHT(A19,4),MID(A19,4,2),LEFT(A19,2)))​
20​	26/09/2019​	=IF(ISNUMBER(A20),A20,DATE(RIGHT(A20,4),MID(A20,4,2),LEFT(A20,2)))​

Sheet: Sheet1

when you will use filter it will show January entry but we have not any January entry in this sheet.

pls check and help

 

[footoo]

There does not appear to be any consistency in the content and formatting of column A.
It seems that it contains a mixture of dates and numbers, and also a mixture of d/m, m/d, dd/mm, mm/dd, etc.
It's difficult to suggest a formula without knowing exactly what column A contains.

Its Showing February and March Month but we have not any entry on that particular month...

Columns A and B in your last post are showing : m/d/yyyy.
So that's why February appears in the filter.
March does not appear in the data you posted, so it should not appear in the filter.

 

[Vishaal]

Columns A data is main data and Columns B data is after code

i hope you can understand now

 

[footoo]

Need to know whether each cell is date or text, and the format of each cell (e.g. d/m/y or m/d/y).

 

[Vishaal]

1. DD-MM-YYYY
2. DD/MM/YYYY
3. Some cells are General