Excel: Text String to Date Conversion

Question

I have a working formula for the following text to date conversion. However, I don't understand why the trailing 0 has to be added in order to show the year in YYYY format.

21.04.2016 converts to 4/21/2016

=(MID(A2,4,2)&"/"&LEFT(A2,2)&"/"&RIGHT(A2,2))+0

It's very simple and straight-forward. However if you remove the 0 at the end of the formula, it will only show 16 instead of 2016. But I could do Right(A2,4) instead of Right(A2,2). But I still like to know why? Anyone? Thanks!

Answer 1

The trailing zero turns the whole thing into a math operation which causes the string (everything to the left of the +0) to be treated as a number.

you could also use *1 instead of +0

=(MID(A2,4,2)&"/"&LEFT(A2,2)&"/"&RIGHT(A2,2))*1

or you could drop the +0 and at the front add -- before the ( and it should all do the same.

=--(MID(A2,4,2)&"/"&LEFT(A2,2)&"/"&RIGHT(A2,2))

Answer 2

As Ed has correctly answered, this is because it is treating your date string as a number.

However, Excel has to interpret the string to get what number it really is, and to do this it relies on regional settings. Under US regional settings, your formula works great, but when I plug it into excel with UK regional settings I get #Value because "04/21/16" isn't a valid date or number in the UK.

In order to avoid this problem, you should convert it to a date using the DATE() function, which will work irrespective of your regional settings.

=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))