automatically add days to a date

[jaspa]

Hi Folks,

This is hopefully going to be easy for you guys, but it's really starting to bug me now!

I'm adding a date in cell A1, I'm entering a code in B1, and I need C1 to equal A1 + so many extra days, However if the final date falls on a weekend then I need to add extra days again to force the date to the next working day (Monday).

Here is what I have so far:
=IF(B1="A",A1+(IF(WEEKDAY(A1+3)=1,4,IF(WEEKDAY(A1+3)=7,5,3))),IF(B1="B",A1+(IF(WEEKDAY(A1+5)=1,6,IF(WEEKDAY(A1+5)=7,7,5)))))

And this works just fine, but I also need to add A1+10, but if I try too add this to the existing formula, I get an error of too many arguments for this function.

Any help much appreciated!!!

[Mikeopolo]

Make a lookup table somewhere:
Text DaysAhead
a 3
b 7
c 10 (as you require)

Give the data range (from a to c plus the values) a name (eg) daysahead

Use the workdays function: c1 =workdays(a1,vlookup(b1,daysahead,2,false)[,holidays])

This will return a date = to the next working day. You will need to install the Analysis pack add-in - Tools, Add-ins, tick the Analysis Pack, OK.

The workday function has an optional third parameter as indicated, which is a range of holiday dates. If you want to allow for holidays, set up a single column range of dates and name it holidays.

Because you may add to that range it may pay to set it up as a dynamic range, thus:

=OFFSET(Sheet1!$G$2,0,0,COUNTA(Sheet1!$G:$G)-1,1)

where the holiday range starts in G2 (change the sheet/cell as required), and has no blank cells.

hth

Mike