Hi,

I need a little help. I have time punches that are coming out of a report
into two columns in Excel. I need it to be in four columns so I can analyze
the data better. Any help would be great.

This is how the data is retrieved €“ Time in is in one column and Time out is
in one column.

Last Name First Name Employee Payroll ID Department Pay Group Worked
Department State Day In time Out time Out Punch Type Hours Earnings Code
Doe John 1000001152 11100 123456 CZHRLY 123456 P Wednesday 1/6/2010
6:08 1/6/2010 12:02 LP 5.9
Doe John 1000001152 11100 123456 CZHRLY 123456 P Wednesday 1/6/2010
12:51 1/6/2010 16:05 3.23


Need data to look like this. Four columns for time in, time out, time in,
time out.


Last Name First Name Employee Payroll ID Department Pay Group Worked
Department State Day In time Out time In time Out time Out Punch
Type Hours Hours Earnings Code
Doe John 1000001152 11100 123456 CZHRLY 123456 P Wednesday 1/6/2010
6:08 1/6/2010 12:02 1/6/2010 12:51 1/6/2010 16:05 LP 5.9 3.23


Does anyone have any ideas, I am open to anything.
Thanks
PB

Excel 2007 PivotTable
Transpose work periods
from rows to columns.
http://www.mediafire.com/file/nw1dzj...02_23_10a.xlsx

Hi there

There is a solution but the display is hindering any response as it's not
really clear (to me at least) which cells you need to split. Also are the
characters in those cells of a uniform nature i.e. do they always have the
same number of letters and numbers?

The following link is to Google docs where I have placed a blank s/s for you
to copy a row of data with headers to show how it appears.

http://spreadsheets.google.com/ccc?k...UpmZ UE&hl=en

We'll take it from there.

--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"PB" wrote:

Hi,

I need a little help. I have time punches that are coming out of a report
into two columns in Excel. I need it to be in four columns so I can analyze
the data better. Any help would be great.

This is how the data is retrieved €“ Time in is in one column and Time out is
in one column.

Last Name First Name Employee Payroll ID Department Pay Group Worked
Department State Day In time Out time Out Punch Type Hours Earnings Code
Doe John 1000001152 11100 123456 CZHRLY 123456 P Wednesday 1/6/2010
6:08 1/6/2010 12:02 LP 5.9
Doe John 1000001152 11100 123456 CZHRLY 123456 P Wednesday 1/6/2010
12:51 1/6/2010 16:05 3.23


Need data to look like this. Four columns for time in, time out, time in,
time out.


Last Name First Name Employee Payroll ID Department Pay Group Worked
Department State Day In time Out time In time Out time Out Punch
Type Hours Hours Earnings Code
Doe John 1000001152 11100 123456 CZHRLY 123456 P Wednesday 1/6/2010
6:08 1/6/2010 12:02 1/6/2010 12:51 1/6/2010 16:05 LP 5.9 3.23


Does anyone have any ideas, I am open to anything.
Thanks
PB

As Rulless said, the display is making it hard to understand. However, when
I copied it over to word (it is tab delim) and converted to a table, i think
I understand what your going for. In the original entry, you have two rows
for each person for each day. (we'll say rows 2 and 3 for argument's sake).
The time-in and time-out are in columns K and L respectively for each row.
You want the finished product to have Time-in, Time-out, time-in, time-out in
rows K, L, M and N respectively, correct?

I think it would probably be most effeciently done with a macro but I'm not
sure how many rows of data you have. You could also move it to a new sheet.
For the sake of having something, we'll call the original shee 'Original'.
In the new sheet, for columns A-L, you can just reference the first sheet
(='Original'!(A1)) and copy down on every other row.

For cell M1, and N1 the formula would be ='Original'!(K2) and
='Original'!(L2) respectively then the final 5 rows would start with
='Original'!(M1) and copy across to finish up the row.

Then you could copy this row of cells down to every other row (to allow for
the fact that you're copying multiple rows into a single row). There may be
a prettier way to do this for a large number of rows but, like i said, my
preference would probably be a macro if you're just going to have to convert
it once per output worksheet. That could be done fairly simply with a couple
for loops.

"PB" wrote:

Hi,

I need a little help. I have time punches that are coming out of a report
into two columns in Excel. I need it to be in four columns so I can analyze
the data better. Any help would be great.

This is how the data is retrieved €“ Time in is in one column and Time out is
in one column.

Last Name First Name Employee Payroll ID Department Pay Group Worked
Department State Day In time Out time Out Punch Type Hours Earnings Code
Doe John 1000001152 11100 123456 CZHRLY 123456 P Wednesday 1/6/2010
6:08 1/6/2010 12:02 LP 5.9
Doe John 1000001152 11100 123456 CZHRLY 123456 P Wednesday 1/6/2010
12:51 1/6/2010 16:05 3.23


Need data to look like this. Four columns for time in, time out, time in,
time out.


Last Name First Name Employee Payroll ID Department Pay Group Worked
Department State Day In time Out time In time Out time Out Punch
Type Hours Hours Earnings Code
Doe John 1000001152 11100 123456 CZHRLY 123456 P Wednesday 1/6/2010
6:08 1/6/2010 12:02 1/6/2010 12:51 1/6/2010 16:05 LP 5.9 3.23


Does anyone have any ideas, I am open to anything.
Thanks
PB