Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Working Days function copied down column
Hi
What i'm wanting to do is add a certain number of working days excluding holidays to a date. I've seen the formula below which works OK. =start_date+IF(days=0,0,SIGN(days)*SMALL(IF((WEEKD AY(start_date+SIGN(days)*(ROW(INDIRECT("1:"&ABS(da ys)*10))),2)<6)*ISNA(MATCH(start_date+SIGN(days)*( ROW(INDIRECT("1:"&ABS(days)*10))),holidays,0)),ROW (INDIRECT("1:"&ABS(days)*10))),ABS(days))) This only allows you to have one date as the start date. What i need is the cells in column A will have different dates in and the cells in column B will have the formula in. I need a formula that can be copied down column B that will pick up the different dates in column A. I can't use the analysis toolpack addin. Thanks in advance for any help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Working Days function copied down column
If I understand what you want just replace each call to start_date with the
cell reference. For example, A1. Then, as you copy down the cell ref will increment accordingly. -- Biff Microsoft Excel MVP "Mally" wrote in message ... Hi What i'm wanting to do is add a certain number of working days excluding holidays to a date. I've seen the formula below which works OK. =start_date+IF(days=0,0,SIGN(days)*SMALL(IF((WEEKD AY(start_date+SIGN(days)*(ROW(INDIRECT("1:"&ABS(da ys)*10))),2)<6)*ISNA(MATCH(start_date+SIGN(days)*( ROW(INDIRECT("1:"&ABS(days)*10))),holidays,0)),ROW (INDIRECT("1:"&ABS(days)*10))),ABS(days))) This only allows you to have one date as the start date. What i need is the cells in column A will have different dates in and the cells in column B will have the formula in. I need a formula that can be copied down column B that will pick up the different dates in column A. I can't use the analysis toolpack addin. Thanks in advance for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making weekend days working days - the system cuts the working tim | Excel Discussion (Misc queries) | |||
Complicated working days function | Excel Worksheet Functions | |||
Lookup function to take into account working days only | Excel Worksheet Functions | |||
WOrkday function - change working days | Excel Worksheet Functions | |||
Getting XL to Calc Working Days Lost without using NETWORKDAYS() Function | Excel Discussion (Misc queries) |