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

Vishaal

Active Member
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
 
Last edited:

footoo

Well-known Member
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

Active Member
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

Well-known Member
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.
 

footoo

Well-known Member
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).
 

Some videos you may like

This Week's Hot Topics

Top