• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

arghh formula melting my cranium

B

Bloodroot

New Member
I have a collumn of dates that have sum formulas to self populate when you enter the first row, each row may have a different formula as these are based on a number of days it takes to complete a task, however this does not take into consideration we are closed for christmas (25/12-1/1).


Is there a formula i can use to look for these dates and to add the 8 days??


Any help would be really aprreciated as my level of excell is very basic.


Many Thanks


Barry
 
Can you upload a sample?


If you're substracting two dates networkdays has an additional paramaeter to exclude a list of holidays. But, it doesn't count weekends.
 
Barry


Firstly, Welcome to the Chandoo.org Forums


Have a look at the Workday Function

Assuming your date is in A1 use
Code: 
=WORKDAY(A1,8)

Which will add 8 days to the value in A1 and not include Sat/Sunday as workdays


Workday can also use a list of Holidays

[code]=WORKDAY(A1,8, Z2:Z10)

Would do the same and include a list of holiday dates in Z2:Z10


If your using Excel 2010 you should use the new Workday.intl function instead as it allows you to customise the weekends to be any week days

=WORKDAY.INTL(A1,8)[/code]
 
Hi cant seem to upload the file but see below:


Planner that auto enters all dates for all tasks, however rather than creating individual template that are effected by days closed i need a formula that will find dates between the 25/12/12 - 1/1/12 but will calculate the amount to move the dates along,


was thinking somethig along the lines of =E4+1 AND =E4+2 IF D4 (Being the closed date)

[pre]
Code: 
No of Days	Start Dates
7	13/09/2012              Just need to add this date to populate whole column
1	22/09/2012              Formula =E3+9
1	23/09/2012                      =E4+1
1	24/09/2012
1	27/09/2012
1	28/09/2012
2	29/09/2012
1	01/10/2012
1	04/10/2012
3	05/10/2012
1	08/10/2012
1	11/10/2012
1	12/10/2012
1	13/10/2012
1	14/10/2012
1	15/10/2012
2	18/10/2012
1	20/10/2012
1	21/10/2012
1	22/10/2012
5	25/10/2012
1	01/11/2012
1	02/11/2012
1	03/11/2012
1	04/11/2012
1	05/11/2012
3	08/11/2012
1	11/11/2012
1	12/11/2012
1	15/11/2012
1	16/11/2012
1	17/11/2012
1	18/11/2012
1	19/11/2012
2	22/11/2012
2	24/11/2012
1	26/11/2012
5	29/11/2012
15	06/12/2012
5	27/12/2012
15	03/01/2013
1	24/01/2013
1	25/01/2013
3	26/01/2013
2	31/01/2013
1	02/02/2013
1	03/02/2013
1	04/02/2013
1	07/02/2013
1	08/02/2013
60	09/02/2013
5	04/05/2013
[/pre]
Hope this helps


Thanks

Barry
 
As Hui mentioned above, the WORKDAY formula should be what you're looking for. In your setup, it would be:

=WORKDAY(B2,A2,$Z$2:$Z$10)


Where Z2:Z10 contains your holidays.
 
You must log in or register to reply here.
Back
Top