Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Pete
 
Posts: n/a
Default 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
  #2   Report Post  
Peo Sjoblom
 
Posts: n/a
Default Would like to add 20 working/weekdays to a date

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 toolsadd-ins and select ATP (Analaysis
ToolPak), follow the directions and keep the office/excel cd handy


--

Regards,

Peo Sjoblom

"Pete" wrote in message
...
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



  #3   Report Post  
bpeltzer
 
Posts: n/a
Default Would like to add 20 working/weekdays to a date

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

  #4   Report Post  
Pete
 
Posts: n/a
Default Would like to add 20 working/weekdays to a date

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
  #5   Report Post  
Peo Sjoblom
 
Posts: n/a
Default Would like to add 20 working/weekdays to a date

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 formatconditional formatting, select cell value is greater than
put 20 in the value box


--

Regards,

Peo Sjoblom

"Pete" wrote in message
...
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





  #6   Report Post  
Pete
 
Posts: n/a
Default Would like to add 20 working/weekdays to a date

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?
  #7   Report Post  
Peo Sjoblom
 
Posts: n/a
Default Would like to add 20 working/weekdays to a date

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

will display as empty if either start or end are blank


--

Regards,

Peo Sjoblom


"Pete" wrote in message
...
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?



  #8   Report Post  
Pete
 
Posts: n/a
Default Would like to add 20 working/weekdays to a date

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

Kind regards,
Pete
  #9   Report Post  
Peo Sjoblom
 
Posts: n/a
Default Would like to add 20 working/weekdays to a date

My Pleasure

--

Regards,

Peo Sjoblom

"Pete" wrote in message
...
Peo, thanks very much for all your help. It's much appreciated.

Kind regards,
Pete



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I create a schedule from a list of dates ? Gavin Morris Charts and Charting in Excel 2 October 28th 10 04:08 PM
NETWORKDAYS - Multiple Date Selection Annabelle Excel Discussion (Misc queries) 3 October 4th 05 07:04 PM
How do I calculate if a date is in a certain time frame? Pe66les Excel Worksheet Functions 19 August 27th 05 11:07 PM
search for latest date Sue Excel Worksheet Functions 2 August 2nd 05 11:09 PM
Recurring annual events using a specific date as a trigger date Bamboozled Excel Worksheet Functions 1 June 6th 05 01:44 PM


All times are GMT +1. The time now is 03:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"