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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]its my format which I want to change in date format[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

Date​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

01-04-2019​

[/td][td]

=TEXT(A3,"mm\/dd\/yyyy")+0​

[/td][td]

=TEXT(A3,"mm/dd/yyyy")​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

02-04-2019​

[/td][td]

=TEXT(A4,"mm\/dd\/yyyy")+0​

[/td][td]

=TEXT(A4,"mm/dd/yyyy")​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

02-04-2019​

[/td][td]

=TEXT(A5,"mm\/dd\/yyyy")+0​

[/td][td]

=TEXT(A5,"mm/dd/yyyy")​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

04-04-2019​

[/td][td]

=TEXT(A6,"mm\/dd\/yyyy")+0​

[/td][td]

=TEXT(A6,"mm/dd/yyyy")​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

05-04-2019​

[/td][td]

=TEXT(A7,"mm\/dd\/yyyy")+0​

[/td][td]

=TEXT(A7,"mm/dd/yyyy")​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

06-04-2019​

[/td][td]

=TEXT(A8,"mm\/dd\/yyyy")+0​

[/td][td]

=TEXT(A8,"mm/dd/yyyy")​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

24-09-2019​

[/td][td]

=TEXT(A9,"mm\/dd\/yyyy")+0​

[/td][td]

=TEXT(A9,"mm/dd/yyyy")​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

4/5/2019​

[/td][td]

=TEXT(A10,"mm\/dd\/yyyy")+0​

[/td][td]

=TEXT(A10,"mm/dd/yyyy")​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]

4/9/2019​

[/td][td]

=TEXT(A11,"mm\/dd\/yyyy")+0​

[/td][td]

=TEXT(A11,"mm/dd/yyyy")​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]

4/17/2019​

[/td][td]

=TEXT(A12,"mm\/dd\/yyyy")+0​

[/td][td]

=TEXT(A12,"mm/dd/yyyy")​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]

4/26/2019​

[/td][td]

=TEXT(A13,"mm\/dd\/yyyy")+0​

[/td][td]

=TEXT(A13,"mm/dd/yyyy")​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]

5/4/2019​

[/td][td]

=TEXT(A14,"mm\/dd\/yyyy")+0​

[/td][td]

=TEXT(A14,"mm/dd/yyyy")​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]

5/4/2019​

[/td][td]

=TEXT(A15,"mm\/dd\/yyyy")+0​

[/td][td]

=TEXT(A15,"mm/dd/yyyy")​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td]

5/21/2019​

[/td][td]

=TEXT(A16,"mm\/dd\/yyyy")+0​

[/td][td]

=TEXT(A16,"mm/dd/yyyy")​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td]

20-07-2019​

[/td][td]

=TEXT(A17,"mm\/dd\/yyyy")+0​

[/td][td]

=TEXT(A17,"mm/dd/yyyy")​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td]

06/08/19​

[/td][td]

=TEXT(A18,"mm\/dd\/yyyy")+0​

[/td][td]

=TEXT(A18,"mm/dd/yyyy")​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

19​

[/td][td]

24-07-2019​

[/td][td]

=TEXT(A19,"mm\/dd\/yyyy")+0​

[/td][td]

=TEXT(A19,"mm/dd/yyyy")​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

20​

[/td][td]

26/09/2019​

[/td][td]

=TEXT(A20,"mm\/dd\/yyyy")+0​

[/td][td]

=TEXT(A20,"mm/dd/yyyy")​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]


Result Data
Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]its my format which I want to change in date format[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

Date​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

01-04-2019​

[/td][td]

01-04-2019​

[/td][td]

04/01/2019​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

02-04-2019​

[/td][td]

02-04-2019​

[/td][td]

04/02/2019​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

02-04-2019​

[/td][td]

02-04-2019​

[/td][td]

04/02/2019​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

04-04-2019​

[/td][td]

04-04-2019​

[/td][td]

04/04/2019​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

05-04-2019​

[/td][td]

05-04-2019​

[/td][td]

04/05/2019​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

06-04-2019​

[/td][td]

06-04-2019​

[/td][td]

04/06/2019​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

24-09-2019​

[/td][td]

24-09-2019​

[/td][td]

09/24/2019​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

4/5/2019​

[/td][td]

05-04-2019​

[/td][td]

04/05/2019​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]

4/9/2019​

[/td][td]

09-04-2019​

[/td][td]

04/09/2019​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]

4/17/2019​

[/td][td]

17-04-2019​

[/td][td]

04/17/2019​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]

4/26/2019​

[/td][td]

26-04-2019​

[/td][td]

04/26/2019​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]

5/4/2019​

[/td][td]

04-05-2019​

[/td][td]

05/04/2019​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]

5/4/2019​

[/td][td]

04-05-2019​

[/td][td]

05/04/2019​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td]

5/21/2019​

[/td][td]

21-05-2019​

[/td][td]

05/21/2019​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td]

20-07-2019​

[/td][td]

#VALUE!​

[/td][td]

20-07-2019​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td]

06/08/19​

[/td][td]

08-06-2019​

[/td][td]

06/08/2019​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

19​

[/td][td]

24-07-2019​

[/td][td]

#VALUE!​

[/td][td]

24-07-2019​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

20​

[/td][td]

26/09/2019​

[/td][td]

26-09-2019​

[/td][td]

09/26/2019​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[footoo]

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

This is not like the data you posted.
For example :
24-9-2019 (is it DD-M-YYYY or D-M-YYYY?)
4/5/2019 (is it D/M/YYYY or M/D/YYYY?)
4/17/2019 (is it M/DD/YYYY or M/D/YYYY?)
Are these examples text or dates?

 

[footoo]

Maybe this : =IF(ISNUMBER(A3),A3,DATEVALUE(A3))

 

[Vishaal]

Thanks footoo,

thanks for giving time and help