Prev: Please help
Next: #DIV/O!
From: ajitexcel on 7 Mar 2010 17:40

can anyone help me with this
a. how to get future date (like WORKDAY Function) but the formula
should include saturdays as work day excluding sundays and holidays.

for example if 2 mar 10 is the starting date then plus 15 days
including 2 holidays (say 5 mar 10 & 9 mar 10) and sundays (i.e. 7 mar
& 14 mar) the to be arrived should be 20 mar 10.
and the formula should also check if the date arrived is a saturday
then it should add following sunday i.e in above case it should become
21 mar 10
:)




--
ajitexcel
From: Roger Govier on 9 Mar 2010 05:25
Hi

The following array entered formula is based upon one written by the
late Frank Kabel.

It depends upon some defined names.
Use Insert>Name>Define>
Days =cell with number of days to add
Start_date =cell containing the start date
holidays =range of cells containing holiday dates

Then use the array entered formula
{=start_date+IF(days=0,0,SIGN(days)*SMALL(IF((WEEKDAY(start_date+SIGN(days)*
(ROW(INDIRECT("1:"&ABS(days)*10))),2)<7)*ISNA(MATCH(start_date+SIGN(days)*
(ROW(INDIRECT("1:"&ABS(days)*10))),holidays,0)),ROW(INDIRECT("1:"&ABS(days)*
10))),ABS(days)))}

Use Control+Shift+Enter (CSE) not just Enter when you save or amend the
formula. Do not type the curly braces { } yourself, Excel will insert
them when you use CSE.

I believe that you meant, when the result ends on a Saturday, use the
following Monday, which would be the 22nd March, not the 21st.
--
Regards
Roger Govier

ajitexcel wrote:
> can anyone help me with this
> a. how to get future date (like WORKDAY Function) but the formula
> should include saturdays as work day excluding sundays and holidays.
>
> for example if 2 mar 10 is the starting date then plus 15 days
> including 2 holidays (say 5 mar 10 & 9 mar 10) and sundays (i.e. 7 mar
> & 14 mar) the to be arrived should be 20 mar 10.
> and the formula should also check if the date arrived is a saturday
> then it should add following sunday i.e in above case it should become
> 21 mar 10
> :)
>
>
>
>
 | 
Pages: 1
Prev: Please help
Next: #DIV/O!