Search code examples
excelfunctiondateformulasexcel-2013

Problems changing date format of date as string

I am currently trying to convert yyyymmdd type of date to a ddmmyyy format.

  • I've tried using DATE function and something like this:

    =DATE(LEFT(A3;4);MID(A3;5;3);RIGHT(A3;7))

    Original date for this function is 20120401 but the formula returns: 16.12.2104.

  • I've tried using functions YEAR, MONTH and DAY instead of LEFT, MID and RIGHT but that's not the right way.

  • I also tried using DATEVALUE but since Excel probably doesn't recognize yyyymmdd as a date, it gives me a #VALUE! error.

  • I've found a couple of solutions for SQL but it isn't my strong side yet - and this should be achievable easily in Excel.

Is there a way to do this in Excel?

Solution

  • Applying =DATE(LEFT(A3;4);MID(A3;5;3);RIGHT(A3;7)) to 20120401 in A3 is effectively to reassemble the component characters as below on the left:

    enter image description here

    These effectively become converted as on the right.

    The solution was simply to exclude the digits highlighted red with:
    =DATE(LEFT(A3;4);MID(A3;5;2);RIGHT(A3;2)) but the locale might have been a factor and when dealing with date serial numbers and/or what appears to be a date but is a string various other issues might have been involved.