how to format this date type?

[cat3appr]

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!

[FlameRetired]

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

[cat3appr]

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

[NickyC]

hi
try this formula

Please Login or Register  to view this content.

[cat3appr]

hi
try this formula

Please Login or Register  to view this content.

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!

[NickyC]

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

[cat3appr]

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

[NickyC]

see the attached

[cat3appr]

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...

[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

[NickyC]

ah, got it I think
try this instead

[cat3appr]

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

[cat3appr]

ah, got it I think
try this instead

yep the last one is correct :=) cheers!

[FlameRetired]

Late comer. Another way. With data in column A.
In B1 and down

Please Login or Register  to view this content.

In C1 and down

Please Login or Register  to view this content.

In D1 and down

Please Login or Register  to view this content.

Then in E1 filled across column F & G and down.

Please Login or Register  to view this content.