Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
How do I calculate if a date is in a certain time frame? | Excel Worksheet Functions | |||
search for latest date | Excel Worksheet Functions | |||
Recurring annual events using a specific date as a trigger date | Excel Worksheet Functions |