Date format not working as expected when extract from text string

This is a format issue.
Kindly change format in custom as :

PHP Code: 

_($* #,##0_);_($* (#,##0);_($* "-"_);_(@_) 


Select B2, Format as short date not a text, edit B2, enter, drag formula down, export to csv

Edit:
I forgot about next columns: change in C2: =--B2 and in D2: =--B2

Originally Posted by iantix

In cell A2; 2016-10-13T11:20:16.000

I have written a formula to extract the date & time, combining them;

=TEXT(LOWER(LEFT(SOURCE!A2,10)&" "&MID(SOURCE!A2,12,5)),"dd/mm/yyyy hh:mm")

I didn't download your file.

This worked for me...

=--TEXT(LEFT(SOURCE!A2,10)&" "&MID(SOURCE!A2,12,5),"yyyy-mm-dd hh:mm")

Then, format in the date/time style that you desire.

Originally Posted by iantix

Is - the -- at the start, what does that actually do to change things up, please?

The TEXT( ) function returns a TEXT value while a true date/time value is a numeric value.

For example, the true date value 1/17/2017 is really just a number formatted to look like a date.

In Excel dates are really just numbers formatted to look like dates. These numbers are commonly referred to as the date serial number. The date serial number is the count of days starting from a base date. In Windows Excel the default base date is January 1, 1900. So, January 1, 1900 is date serial number 1.

Jan 1 1900 = date serial number 1
Jan 2 1900 = date serial number 2
Jan 3 1900 = date serial number 3
Jan 4 1900 = date serial number 4
Jan 5 1900 = date serial number 5
etc
etc
Jan 1 1975 = date serial number 27395
Jan 1 2000 = date serial number 36526
Jan 1 2017 = date serial number 42736

You can see the date serial number by entering some date in a cell and then changing that cells format to General or Number.

So, when we use the TEXT( ) function:

TEXT(LEFT(SOURCE!A2,10)&" "&MID(SOURCE!A2,12,5),"yyyy-mm-dd hh:mm")

This returns a TEXT string that looks like a date/time: 2016-10-13 11:20

So we need to convert that TEXT value into a true numeric date/time value. One way to do that is to use the double unary minus --.

--TEXT(LEFT(SOURCE!A2,10)&" "&MID(SOURCE!A2,12,5),"yyyy-mm-dd hh:mm")

Now the result is the number 42656.47222

Then we can format that number to display in whatever date/time we desire.

(pretty sure the 'lower' in mine was superfluous also)

Yes, it was not needed.

Interesting your formula has year/month/day in reverse to mine, yet displays as 11/05/2016 (for example) on mine.

Yeah, the date formats from country to country vary but the yyyy-mm-dd seems to be a fairly "standard" format recognized internationally.

You're welcome. Thanks for the feedback!