Help with formatting date

[TonyF57]

I am importing data and the date is in the incorrect format and has a green triangle top left of each date cell.

It gives me the option to ignore error, change date format by converting XX to 19XX or convert XX to 2021

I can resolve it manually by selecting the column and changing format to xx21 but I'd like to do it via a macro.

The macro works up until the point where I select the error dropdown, presumably that is not recordable.

Is there an automatic way of doing this?

Tony

There can be up to 10,000 entries.

I am importing data and the date is in the incorrect format and has a green triangle top left of each date cell.

It gives me the option to ignore error, change date format by converting XX to 19XX or convert XX to 2021

I can resolve it manually by selecting the column and changing format to xx21 but I'd like to do it via a macro.

The macro works up until the point where I select the error dropdown, presumably that is not recordable.

Is there an automatic way of doing this?

Tony

There can be up to 10,000 entries.

Date Format.xlsx
	B
1	30/04/21
2	30/04/21
3	30/04/21
4	30/04/21
5	30/04/21
Sheet1

 

[Marc L]

​

As it is just a bad import so the better is to directly work on the import file rather than after the importation …​

 

[Dave Patton]

The Date would convert OK if your system has Regional Settings of International
Try Data Text to Columns
clear delimiters
Date use DMY
Specify where you want the result ( for my example I used B4)
Format to your preference

Date and Time 2021.xlsm
	A	B	C	D	E
1
2	30/04/21	30-Apr-2021	Regional Settings International
3
4	30/04/21	2021-04-30
5	30/04/21	2021-04-30
6	30/04/21	2021-04-30
7	30/04/21	2021-04-30
8
11a

Cell Formulas
Range	Formula
B2	B2	=A2+0

 

[TonyF57]

​

As it is just a bad import so the better is to directly work on the import file rather than after the importation …​

That's not an option unfortunately, the source won't even allow export of data, everything is copy and paste which is so tedious in this day and age.

 

[TonyF57]

The Date would convert OK if your system has Regional Settings of International
Try Data Text to Columns
clear delimiters
Date use DMY
Specify where you want the result ( for my example I used B4)
Format to your preference

Date and Time 2021.xlsm
	A	B	C	D	E
1
2	30/04/21	30-Apr-2021	Regional Settings International
3
4	30/04/21	2021-04-30
5	30/04/21	2021-04-30
6	30/04/21	2021-04-30
7	30/04/21	2021-04-30
8
11a

Cell Formulas
Range	Formula
B2	B2	=A2+0

Thanks Dave, my settings are UK, I've looked but don't see an international option.

I was hoping to not use a formula as it's another action, I was hoping to find a macro solution if at all possible.

 

[Dave Patton]

Quote "Thanks Dave, my settings are UK, I've looked but don't see an international option. " (1)
"I was hoping to not use a formula as it's another action, I was hoping to find a macro solution if at all possible." (2)

(1) UK is part of the International community that uses Regional Settings dd-mm-yy.
(2) It might be useful to know what the challenge is and then use VBA if necessary.

What is the length of the text in say cell A2?

Date and Time 2021.xlsm
	A	B	C
2	30/04/21	30-Apr-2021	8
11a

Cell Formulas
Range	Formula
B2	B2	=A2+0
C2	C2	=LEN(A2)

In Canada, we use both USA standard and International standard.

In the UK, the data that you showed should show proper dates.

Did you try coercing the text? Copy a blank cell and Paste Special Add.

Please post a sample of your data with the forum's tool XL2BB.

 

[Marc L]

​

If the date Windows Regional Settings is the same than the text dates column so just try a VBA codeline like Range.FormulaLocal = Range.Value2 …​

​

The other obvious way is to just convert the column via the easy Excel feature Text To Columns in the Data tab whatever manually or under VBA …​

 

[TonyF57]

​

If the date Windows Regional Settings is the same than the text dates column so just try a VBA codeline like Range.FormulaLocal = Range.Value2 …​

​

The other obvious way is to just convert the column via the easy Excel feature Text To Columns in the Data tab whatever manually or under VBA …​

Thanks Marc,

Hadn't thought of Text To Columns as an option, selected the range, Delimited, Other forgot to specify the delimiter (/) it now appears to be correct looks good but unable to record it as a macro.

Not sure how I would try a vba codeline but at least I have a relatively easy solution now.

Thank you

 

[TonyF57]

Quote "Thanks Dave, my settings are UK, I've looked but don't see an international option. " (1)
"I was hoping to not use a formula as it's another action, I was hoping to find a macro solution if at all possible." (2)

(1) UK is part of the International community that uses Regional Settings dd-mm-yy.
(2) It might be useful to know what the challenge is and then use VBA if necessary.

What is the length of the text in say cell A2?

Date and Time 2021.xlsm
	A	B	C
2	30/04/21	30-Apr-2021	8
11a

Cell Formulas
Range	Formula
B2	B2	=A2+0
C2	C2	=LEN(A2)

In Canada, we use both USA standard and International standard.

In the UK, the data that you showed should show proper dates.

Did you try coercing the text? Copy a blank cell and Paste Special Add.

Please post a sample of your data with the forum's tool XL2BB.

What is the length of the text in say cell A2?

Not sure what the {Range=...Range] relates too, beyond my brain capacity )

Thought I had posted a sample of the data in the first post but it appears to be an image, I will look into that and see if I can get it uploaded.

 

[Dave Patton]

Regarding to your reply to Marc

To use Text to Columns with Date issues

Open Text to Columns
move to Delimiters remove all checks
move to next then Date select DMY and select where to put result or change in place
press enter