The data look like CSV which is comma seperated data because there is a comma
between the date and times. Text to columns (or importing) will place the
date and time in two diffferent columns.

The date 2009-04-13 is the international standard which shouldn't require
any changes. the time you will need to replace the period with the dot.

Once the date and time are corrrect to combinat them is simply adding the
two numbers together.

A macro can bewritten to modify the data automatically. Not sure if it
better to modify the worksheet or modify the text file before importing the
data. the information you posted need more details before I can give an
better answer.

"Sara" wrote:

Hi there,

I have a huge spreadsheet that is downloaded out of another program every
week listing all staff and their rosters for the week.

The dates and times are imported in these format 2009-04-13,
14.55.00.000000. Is there a way to convert to 13/04/2009 & 14:55 or similiar
so excel recognises as dates and times, other than find/replace?

The data lists each employee as follows:
ID Surname Start Date Start Time Attnd Type End Date End Time
1 Smith 14/04/2009 14:55 STD 14/04/2009 22:30
1 Smith 15/04/2009 14:55 STD 15/04/2009 22:30
1 Smith 16/04/2009 14:55 STD 16/04/2009 22:30
2 Jones 14/04/2009 17:30 STD 14/04/2009 23:30
2 Jones 15/04/2009 17:30 STD 15/04/2009 23:30
2 Jones 16/04/2009 17:30 STD 16/04/2009 23:30



Is it possible to change this to:

Start End Start End Start End
ID Surname 14/04 14/04 15/04 15/04 16/04 16/04
1 Smith 14:55 22:30 14:55 22:30 14:55 22:30
2 Jones 17:30 23:30 17:30 23:30 17:30 23:30