how to format this date type?

hello

i need to format this .xls with the date as follow
Screenshot - 20.06.2017 , 00_51_53.png

into this one
Screenshot - 20.06.2017 , 00_53_53.png

so that this bizzarre date format 2017-06-20T00:00:00+01 will become YYYY MM DD HH MM SS

file attached.

any input will be appreciated! thanks!

Originally Posted by FlameRetired

What happens to the 20, the T and where did the 17 come from in column C?

the second (target) example is from another file, ignore the values and focus on the procedure - this 2017-06-20T00:00:00+01 should be formatted into YYYY MM DD HH MM SS

hi
try this formula

=DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))+TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))

Originally Posted by NickyC

hi
try this formula

=DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))+TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))

thanks, but no, something is not right, i have copied that formula and this is what i got
Screenshot - 20.06.2017 , 01_23_20.png
the date appears as 20.06.2017 ... this is not what I need, like i mentioned above, id need the date to be formatted as:

YYYY MM DD HH MM SS


to avoid missunderstanding, can you upload the .xls with the formula so that the date comes already formatted?

many thanks!

see the attached

Originally Posted by NickyC

see the attached

thanks, but sorry , it is wrong, i'll try to explain again: this is what i need to see:

Screenshot - 20.06.2017 , 01_34_42.png

not JUN.. and not everything in the same cell...

hi
it looks like the formula is returning a date value but you need to format the cells to "YYY MMM DD HH:MM"

i dont know how to do that.. please the best way, can i get the excel file with the formula so that I will see what i need to see?

cheers

to set the format
on the home tab select
format > format cells > custom

type this in the box

yyyy mmm ddd hh:mm

and press ok

Originally Posted by NickyC

to set the format
on the home tab select
format > format cells > custom

type this in the box

yyyy mmm ddd hh:mm

and press ok

I did it, it didn't work... it was formatted wrongly...
Screenshot - 20.06.2017 , 01_43_43.png

please, kindly upload the xls with the date formatted exactly as explained.. so that i don't need to be any other step



thank you

ah, got it I think
try this instead

Originally Posted by NickyC

ah, got it I think
try this instead

yep the last one is correct :=) cheers!