Would like to add 20 working/weekdays to a date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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
 
Back
Top