| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to Enter Dates
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 Can anyone help me with a formula that will do this? Any help is appreciated. Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to Enter Dates
Assuming the dates in row 1 and the employee numbers in column A, one
way: B2: =SUMPRODUCT(--(Sheet1!$A$2:$A$100=$A2),--(Sheet1!$B$2:$B$100=B$1)) Copy down and across as necessary. In article , Jacq wrote: 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 Can anyone help me with a formula that will do this? Any help is appreciated. Thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to Enter Dates
Thanks so much for your help. It worked!
"JE McGimpsey" wrote: Assuming the dates in row 1 and the employee numbers in column A, one way: B2: =SUMPRODUCT(--(Sheet1!$A$2:$A$100=$A2),--(Sheet1!$B$2:$B$100=B$1)) Copy down and across as necessary. In article , Jacq wrote: 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 Can anyone help me with a formula that will do this? Any help is appreciated. Thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to Enter Dates
Try this:
Sheet1 A2:A7 = 1233 12/1/06 1233 12/2/06 1233 12/3/06 1234 12/1/06 1234 12/2/06 1234 12/3/06 Sheet2 B1:F1 = 12/1/06 12/2/06 12/3/06 12/4/06 12/5/06 Enter this formula on Sheet2 B2: =SUMPRODUCT(--(Sheet1!$A$2:$A$7=$A2),--(Sheet1!$B$2:$B$7=B$1)) Copy across then down Biff "Jacq" wrote in message ... 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 Can anyone help me with a formula that will do this? Any help is appreciated. Thanks |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to Enter Dates
Thanks so much for your help. It worked!
"T. Valko" wrote: Try this: Sheet1 A2:A7 = 1233 12/1/06 1233 12/2/06 1233 12/3/06 1234 12/1/06 1234 12/2/06 1234 12/3/06 Sheet2 B1:F1 = 12/1/06 12/2/06 12/3/06 12/4/06 12/5/06 Enter this formula on Sheet2 B2: =SUMPRODUCT(--(Sheet1!$A$2:$A$7=$A2),--(Sheet1!$B$2:$B$7=B$1)) Copy across then down Biff "Jacq" wrote in message ... 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 Can anyone help me with a formula that will do this? Any help is appreciated. Thanks |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to Enter Dates
You're welcome. Thanks for the feedback!
Biff "Jacq" wrote in message ... Thanks so much for your help. It worked! "T. Valko" wrote: Try this: Sheet1 A2:A7 = 1233 12/1/06 1233 12/2/06 1233 12/3/06 1234 12/1/06 1234 12/2/06 1234 12/3/06 Sheet2 B1:F1 = 12/1/06 12/2/06 12/3/06 12/4/06 12/5/06 Enter this formula on Sheet2 B2: =SUMPRODUCT(--(Sheet1!$A$2:$A$7=$A2),--(Sheet1!$B$2:$B$7=B$1)) Copy across then down Biff "Jacq" wrote in message ... 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 Can anyone help me with a formula that will do this? Any help is appreciated. Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Enter 2 dates in same excel cell | Excel Discussion (Misc queries) | |||
| Enter dates and return availability | Excel Worksheet Functions | |||
| What does hitting Ctrl + Shift + Enter to enter a formula do??? Help a n00b out. | Excel Worksheet Functions | |||
| How to NOT enter dates? | New Users to Excel | |||
| How to enter dates? | New Users to Excel | |||
Hybrid Mode
