Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Calculate matching cells
Hi All, I have a challange that I know you can help with. I am trying to find the networkdays between D2 & D3 if A3 matches A2. Can you help? A B C D 478630 N512A 010 6/23/05 478630 N505A 020 6/23/05 478630 N524A 030 6/24/05 478630 N547A 040 6/27/05 478633 N512A 010 6/23/05 478633 N505A 020 6/23/05 478633 N524A 030 6/23/05 478633 N547A 040 6/27/05 478633 S561A 050 6/27/05 478633 S561A 060 6/27/05 478633 S464A 080 7/11/05 478633 S250A 090 7/9/05 478633 S434A 100 7/15/05 478633 S446A 120 7/15/05 478636 N512A 010 6/23/05 478636 N505A 020 6/24/05 478636 N524A 030 6/24/05 478636 N547A 040 6/27/05 Thanks sharder -- sharder ------------------------------------------------------------------------ sharder's Profile: http://www.excelforum.com/member.php...o&userid=25488 View this thread: http://www.excelforum.com/showthread...hreadid=389351 |
#2
|
|||
|
|||
in cell e3, type
=IF(A3=A2,NETWORKDAYS(D2,D3),"") if you want to add holidays to that, you can add a comma and range after the d3 and before the end paren. - the range showing holiday dates. You probably should sort by column a and then by d - otherwise you could end up with negative numbers as is one of the cases here. hth, Dave "sharder" wrote: Hi All, I have a challange that I know you can help with. I am trying to find the networkdays between D2 & D3 if A3 matches A2. Can you help? A B C D 478630 N512A 010 6/23/05 478630 N505A 020 6/23/05 478630 N524A 030 6/24/05 478630 N547A 040 6/27/05 478633 N512A 010 6/23/05 478633 N505A 020 6/23/05 478633 N524A 030 6/23/05 478633 N547A 040 6/27/05 478633 S561A 050 6/27/05 478633 S561A 060 6/27/05 478633 S464A 080 7/11/05 478633 S250A 090 7/9/05 478633 S434A 100 7/15/05 478633 S446A 120 7/15/05 478636 N512A 010 6/23/05 478636 N505A 020 6/24/05 478636 N524A 030 6/24/05 478636 N547A 040 6/27/05 Thanks sharder -- sharder ------------------------------------------------------------------------ sharder's Profile: http://www.excelforum.com/member.php...o&userid=25488 View this thread: http://www.excelforum.com/showthread...hreadid=389351 |
#3
|
|||
|
|||
Here is an alternative =IF(A2=A3,ABS(NETWORKDAYS(D2,D3))-(D2=D3),"") ABS .... will always give you the positive value -(D2=D3) .... if both values are a Friday -- 0 days instead of 1 day ...or adjust to the way you want it to be Hope it helped Ola Sandström -- olasa ------------------------------------------------------------------------ olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760 View this thread: http://www.excelforum.com/showthread...hreadid=389351 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do you "select locked cells" w/o "select unlocked cells"? | Excel Discussion (Misc queries) | |||
sumif depending on 2 cells matching | Excel Worksheet Functions | |||
requires that merged cells must be identically sized? | Excel Discussion (Misc queries) | |||
Unlocking Cells when a worksheet is protected... | Excel Discussion (Misc queries) | |||
calculate the sum of cells where values are between 6 and 10 in e. | Excel Worksheet Functions |