CODEWITHSUNDEEP
exceldateformat
Rind
Rind

Reputation: 333

Excel text to date format issue

I have the same issue as this OP when trying to change the date format

Excel 2010 date formatting not working on some cells but on others

The solution does work on converting those 'Text' to date format. However for the other datas in the same column which are in 'Date' format they give a #VALUE! error as shown in the image below when the entire column uses the formula.

Is there anyway to resolve this issue other than manually keying in the date which does not need to be fix, as I'm afraid in the future I might have more than a few thousands date to manually change

The formula I've used is this which the entire column uses,

=DATE(RIGHT(A2,4),LEFT(A2,FIND("/", A2,2)-1),MID(A2,FIND("/",A2)+1,2))

enter image description here

Upvotes: 0

Views: 298

Answers (1)

teylyn
teylyn

Reputation: 35900

The data in column A is a mixture of text that looks like dates, and real dates. All rows with the error have the dates as real dates and the left/right/mid functions don't work on the format but on the underlying value, which in the case of the dates is a number. Try it just =RIGHT(A4,4) to see what I mean.

In order to run the formula, all data needs to be text. You can quickly convert the data to text by selecting column A, then click Data > Text to columns > Next > Next > Select "Text" in step 3 and click Finish.

The data will be converted to text and all your error values will disappear.

Upvotes: 1

Related Questions