Networkdays

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

Guest

I have a s/s that is time and action calendar. That is column A lists the tasks; column B lists the time (days) needed to accomplish the task and Column C returns the date corresponding to column B + start date (located in C5).

So, C5=10/1/03
B6=26 days
So, C6=should equal 10/27/03.

But, how can I write this so that the date returned (and still formatted as a date) is a valid weekday; (not a weekend or a holiday).

Please help...I am so stumped!
Thanks in advance!
 
Marcy,

Sounds like you just need to use the WORKDAY function
instead of NETWORKDAYS. See below:

A1 = 10/1/2003
B1 = 26

If I enter =WORKDAY(A1,B1) in cell C1, I get 11/6/2003 in
cell C1, which excludes weekends. You can also create a
table of holidays and use them as "excluded days" in the
formula as well.

Eric
 
=WORKDAY(C5+B6,0)
--
Kind Regards,

Niek Otten

Microsoft MVP - Excel
Marcy said:
I have a s/s that is time and action calendar. That is column A lists the
Click to expand...
tasks; column B lists the time (days) needed to accomplish the task and
Column C returns the date corresponding to column B + start date (located in
C5).
So, C5=10/1/03
B6=26 days
So, C6=should equal 10/27/03.

But, how can I write this so that the date returned (and still formatted
Click to expand...
as a date) is a valid weekday; (not a weekend or a holiday).
 
Thank you both so much for your help!
-----Original Message-----
I have a s/s that is time and action calendar. That is
Click to expand...
column A lists the tasks; column B lists the time (days)
needed to accomplish the task and Column C returns the
date corresponding to column B + start date (located in
C5).
So, C5=10/1/03
B6=26 days
So, C6=should equal 10/27/03.

But, how can I write this so that the date returned (and
Click to expand...
still formatted as a date) is a valid weekday; (not a
weekend or a holiday).
 
One more question, please...
Is there any way to include SATURDAY as a workday?
-----Original Message-----
I have a s/s that is time and action calendar. That is
Click to expand...
column A lists the tasks; column B lists the time (days)
needed to accomplish the task and Column C returns the
date corresponding to column B + start date (located in
C5).
So, C5=10/1/03
B6=26 days
So, C6=should equal 10/27/03.

But, how can I write this so that the date returned (and
Click to expand...
still formatted as a date) is a valid weekday; (not a
weekend or a holiday).
 
Here's a non ATP solution by Daniel Maher that will count Mon - Sat between
start date in A1 and
end date in A2 and where the range F2:F11 contains the public holidays
 
Use the Weekday function to determine if the date of Column C is not equal than Sunday or Saturday (there´s a number to determine that. Take a look in Excel´s Help)

There are two functions to work with days between Monday and Friday (Workday e Networkday) and holidays, but you have to write the holidays in a range.

I think if you write the holidays in a range and use the functions IF e VLOOKUP you may to do what you need
 
How about posting the darn formula


=1+A2-A1-INT((A2-WEEKDAY(A2)-A1+8)/7)-SUMPRODUCT((F2:F12>=A1)*(F2:F12<=A2)*(
WEEKDAY(F2:F12)<>1))
 
Thanks, Peo. Here's my FINAL question on this issue:

If cell C12=1/2/2004
And cell B13=44 days (total; not just working days)
How can I return a date in cell C13 that will fall on the
next valid, even if the total days of 44 falls on a
Saturday or Sunday or holiday?

Tjat's my LAST question...(today)...I promise!
 
It depends on what country you are in since public holidays differ, assuming
you live in the US and 1/2/2004
means January 2nd 2004 and you want to add 44 days but you want to go to the
next working day so in case there you "land" on a Saturday or Sunday it will
return the coming Monday

This will give you the next Monday if C12+B13 is on a weekend day

=IF(WEEKDAY(C12+B13,2)>5,C12+B13-WEEKDAY(C12+B13,2)+8,C12+B13)

You can include a range of holidays and test the day against it as well
 
Forgive my ignorance, Peo but please advise where I would
insert my list of holidays into this formula. I have
them set up on a separate worksheet in cells A2:A22.
Thanks!
 
Back
Top