Would like to add 20 working/weekdays to a date

Ideally I would like a formula that will add 20 working days (British
calendar) to a date in another cell. I don't know if this is possible so as
an alternative, what would the formula be for 20 weekdays?

Many thanks,
Pete

Look at WORKDAY, it is not a native excel function but it comes
with excel/office and it can be either installed when you first install
office/excel or it can be added later

=WORKDAY(A1,20,Holidays)

where A1 is the start date, 20 is the workdays and Holidays is range with
public holidays

=WORKDAY(A1,20,H1:H11)

where you would put each holiday date in H1:H11

if you get a name error, do tools>add-ins and select ATP (Analaysis
ToolPak), follow the directions and keep the office/excel cd handy


--

Regards,

Peo Sjoblom

"Pete" <[email protected]> wrote in message
news:[email protected]...
> Ideally I would like a formula that will add 20 working days (British
> calendar) to a date in another cell. I don't know if this is possible so
as
> an alternative, what would the formula be for 20 weekdays?
>
> Many thanks,
> Pete

Without creating a formula, my approach would be:
Add 28 calendar days. (Just add the number 28 to the starting date)
If that result is a holiday, add another day (use a vlookup to check).
If that result is a holiday, add another day (repeat this step for as many
consecutive holidays as your calendar provides).
If that result is a Saturday, add another day (use the IF and WEEKDAY
functions).
If that result is a Sunday, add another day.
Hopefully that gets you started. --Bruce


"Pete" wrote:

> Ideally I would like a formula that will add 20 working days (British
> calendar) to a date in another cell. I don't know if this is possible so as
> an alternative, what would the formula be for 20 weekdays?
>
> Many thanks,
> Pete

That's perfect thanks! My next question (hopefully my last) relates to the
addition to two further columns.

The two columns we discussed refer to the date a request was received(A1)
and the target date (+20 days) by which we should respond(A2). A third column
will then log the date we actually responded(A3)...

I would like a fourth column to log the number of working days between the
date the request was received(A1) and our actual response date(A3). I can't
work out how to integrate WORKDAYS into a simple '=A3-A1' formula.

Also, to further complicate things, how would I get a value greater that 20
(ie a late response) to show up in red?

Thanks again for your help.

Pete

1.

=NETWORKDAYS(Start_date,End_date,Holidays)

with start date in A1 and end in B1 and holidays in H1:H11


=NETWORKDAYS(A1,B1,H1:H11)

2.

Select the range with the number of days

do format>conditional formatting, select cell value is greater than
put 20 in the value box


--

Regards,

Peo Sjoblom

"Pete" <[email protected]> wrote in message
news:[email protected]...
> That's perfect thanks! My next question (hopefully my last) relates to the
> addition to two further columns.
>
> The two columns we discussed refer to the date a request was received(A1)
> and the target date (+20 days) by which we should respond(A2). A third
column
> will then log the date we actually responded(A3)...
>
> I would like a fourth column to log the number of working days between the
> date the request was received(A1) and our actual response date(A3). I
can't
> work out how to integrate WORKDAYS into a simple '=A3-A1' formula.
>
> Also, to further complicate things, how would I get a value greater that
20
> (ie a late response) to show up in red?
>
> Thanks again for your help.
>
> Pete

Thanks again.

BTW I find that the NETWORKDAYS is inclusive so has to be adjusted by '-1'.

One final question: is it possible to populate the relevent columns with the
formulas without them showing up until until values are entered into the
adjacent cells?

=IF(OR(A1="",B1=""),"",formula)

will display as empty if either start or end are blank


--

Regards,

Peo Sjoblom


"Pete" <[email protected]> wrote in message
news:[email protected]...
> Thanks again.
>
> BTW I find that the NETWORKDAYS is inclusive so has to be adjusted by
'-1'.
>
> One final question: is it possible to populate the relevent columns with
the
> formulas without them showing up until until values are entered into the
> adjacent cells?

Peo, thanks very much for all your help. It's much appreciated.

Kind regards,
Pete

My Pleasure

--

Regards,

Peo Sjoblom

"Pete" <[email protected]> wrote in message
news:[email protected]...
> Peo, thanks very much for all your help. It's much appreciated.
>
> Kind regards,
> Pete