Data Format

[boomshank]

Hello,

Every day i extract data from one of our systems and i have to enter it into an excel document and format it. I want to create a macro to make all of the changes for me but there is one section i am stuck on.

In one of my columns i have a date and time however it is layed out like:

2009-04-16-08:45:52
2009-04-15-13:12:10

How can i remove the - between the date and time without doing it manually?

Any help would be much appreciated

[zbor]

=replace(d8;11;1;" ")

[boomshank]

Thanks Zbor but once it has been replaced i then have to format the data into into date format dd/mm/yyyy which does not work.

[mubashir aziz]

i think you are not clear about your requirements if you want date format from dd-mm-yy to dd/mm/yy then simply change it from

Format > Cell > Number > Date

Now select the different date format

or do it Format > Cell > Number > Custom and in Type write dd/mm/yy.

[boomshank]

Mubashir as stated in my first post the data is set out like 2009-04-22-08:15:03 so i cannot format the cells into a date format as the - between the date and time is stopping it.

The replace formula removed the - however i was still unable to format the cell.

[martindwilson]

with that in column A starting in A1 then in b1 =DATEVALUE(LEFT(A1,10)) format cell dd/mm/yy
and C1 =TIMEVALUE(RIGHT(A1,8)) format cells HH:mm:ss
or simply combine them in b1
put

=DATEVALUE(LEFT(A1,10))+TIMEVALUE(RIGHT(A1,8)) format cell dd/mm/yy HH:mm:ss