Data Conversion - times, dates & rows

[Sara]

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

 

[joel]

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]

Hi Joel,

This isn't a CSV file. It's downloaded straight into excel from PeopleSoft,
so I cannot edit at the source. Numbers are recognised as text.

After a lot of experimentation I've figured out the formula to make excel
recognise the dates/times as such. Is there a shorter/cleaner version of
this?
X2 being the time & W2 being the date
=VALUE(CONCATENATE(LEFT(X2,2),":",MID(X2,4,2),))+VALUE(W2)

I want to be able to transpose the data by employee.

The sheet, simplified is
EMPLOYEE DAY START END
Jones Mon 0800 1600
Jones Tue 0800 1600
Jones Wed 0800 1600
Smith Mon 2300 0800
Smith Tue 2300 0800
Smith Wed 2300 0800

I need this to convert to something like
Employee MonStart MonEnd TuesStart TuesEnd WedStart WedEnd
Jones 0800 1600 0800 1600 0800 1600
Smith 2300 0800 2300 0800 2300
0800

I need something like a PivotTables but have the data item to not be a
calculation

 

[joel]

Your formula is good. You could split the data using Text-To-column and
using delimited with a comma seperator. the only thing that would be shorter
is to replace "CONCATENATE" with the "&"

=VALUE(LEFT(X2,2)&":"&MID(X2,4,2))+VALUE(W2)

 

[Sara]

Awesome

Any ideas on the other part?

Your formula is good. You could split the data using Text-To-column and
using delimited with a comma seperator. the only thing that would be shorter
is to replace "CONCATENATE" with the "&"

=VALUE(LEFT(X2,2)&":"&MID(X2,4,2))+VALUE(W2)

 

[joel]

the other parts can't be simplified.

Awesome

Any ideas on the other part?