How do I create a formula, or an Access file to calculate weeks wo

[Jacq]

I have a big project that I just cannot figure out. I need to calculate how
many weeks each employee works.

In an excel file I have a list of employee numbers and the dates they work
throughout the year. Our weeks are : Week one is 12/21/06 - 12/27/07, week 2
is 12/28/06 - 1/3/07, ect.

For example, what I would like to do, is have one worksheet with the dates
and employee numbers.
On the other worksheet, I would like to have a list of dates and employees
numbers. I would like the formula to put an x in the date that each employee
works. I tried to put all of the dates across the top of the excel file going
right to left, but there are not enough columns and I couldn't add more.

I just cant figure it out. Would it be easier to make something in access?

Any help would be appreciated.

 

[Bob I]

Through Office 2003, you are limited to 256 columns. In Office 2007 it
goes to 16384 columns. If you must do it the way you are envisioning it,
you will need to upgrade.

 

[Jacq]

Is there a formual that can be used for this?

Through Office 2003, you are limited to 256 columns. In Office 2007 it
goes to 16384 columns. If you must do it the way you are envisioning it,
you will need to upgrade.

 

[Bob I]

First a formula won't upgrade your Office version. As to doing it
another way, you will need to be more precise as to what you actually
want to accomplish.

 

[Jacq]

Thank you.

I have one worksheet that lists employee numbers in one column and dates
they have worked in the second column:
Emp # Date Worked
1233 12/1/06
1233 12/2/06
1233 12/3/06
1234 12/1/06
1234 12/2/06
1234 12/3/06

The other worksheet lists employee numbers in one column, then calendar
dates are listed along the top row:
12/1/06 12/2/06 12/3/06
1233
1244

What I would like to do, is pull the information from the worksheet with the
dates worked and have it put a 1 in the cell if that paricular employee
number worked that day, and a 0 in the cell for days not worked:

12/1/06 12/2/06 12/3/06 12/4/06 12/5/06
1233 1 1 1 0 0
1244 1 1 1 0 0

Thanks

 

[Bob I]

If you "mash" column 1 and 2 together in the first sheet you may use
"Match" on the second sheet to do the comparison.

For instance

12/1/06 12/2/06 12/3/06
1233
1244

IF(MATCH("1233 12/1/06",Sheet1!A1:A11,0),1,0)

Will return a 1 or a #N/A

 

[Jacq]

Mash? I dont know what that is. Can you explain it to me.

Thanks for your help.

 

[Bob I]

Column A contains everything and B is empty thusly
A B
1233 12/1/06

instead of

A B
1233 12/1/06

 

[Bob I]

Humm, that didn't translate well, I guess the Tabs got compressed.
Should look more like this

A B
1233 12/1/06

 

[Jacq]

Thank you very much for your help.

Humm, that didn't translate well, I guess the Tabs got compressed.
Should look more like this

A B
1233 12/1/06