help creating helper column
 

I have a situation where I need to compare week1 from one year to week1 from
another year. The problem is that I have the dates, amounts, and categories
in a raw dump from an external application in a text file and am able to
import it into Excel. The issue that I need to do is to be able to create
another column that will contain the week identifier based on the date
column.
example:

Category Date Amount
35 4/18/2005 120.00
35 4/18/2005 170.00
36 4/19/2005 322.00
..
..
..
45 4/22/2006 118.00
46 4/23/2006 131.00
45 4/23/2006 96.00

With a helper column, I should be able to create a pivot table that is based
on the helper column (week number), instead of the date so I can get totals
for the week instead of for the day. Does anyone have any suggestions on
how
to create this helper column? The data source has over 16000 lines and I
don't want to have to manually enter values (even with copy and paste) for
this many lines of data.

Any and all assistance will be greatly appreciated.

Thanks in advance,
Allen
ps Posted in admin as well (managed newsgroup). Now, I take it to the REAL
experts! Thanks.
--

help creating helper column
 

Have faith my friend... Excel has you covered!
Make sure your have the analysis toolpack selected under add-ins, then
use the =weeknum() formula. It's that easy. I would suggest after
your enter the formula and get the results, that you paste over the
formula with the values so that your sheet won't have to recalc all
those rows on each calculation, and that if you share it with someone
who doens't have the analysis tolpack installed they won't have an
error.

Regards,
Jamie
Allen Clark wrote:
I have a situation where I need to compare week1 from one year to week1 from
another year. The problem is that I have the dates, amounts, and categories
in a raw dump from an external application in a text file and am able to
import it into Excel. The issue that I need to do is to be able to create
another column that will contain the week identifier based on the date
column.
example:

Category Date Amount
35 4/18/2005 120.00
35 4/18/2005 170.00
36 4/19/2005 322.00
.
.
.
45 4/22/2006 118.00
46 4/23/2006 131.00
45 4/23/2006 96.00

With a helper column, I should be able to create a pivot table that is based
on the helper column (week number), instead of the date so I can get totals
for the week instead of for the day. Does anyone have any suggestions on
how
to create this helper column? The data source has over 16000 lines and I
don't want to have to manually enter values (even with copy and paste) for
this many lines of data.

Any and all assistance will be greatly appreciated.

Thanks in advance,
Allen
ps Posted in admin as well (managed newsgroup). Now, I take it to the REAL
experts! Thanks.
--