• 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.

Setting Up Due Dates using Work Days only

D

dlrouse518

New Member
Hello - My questions is that I have a log that I have set up to track due dates. I have set up a V-Lookup to track tasks and how long those tasks take. So if my task is to lawn cutting @ dayton & the due date is in 2 days. How do I populate that I only want the due dates to be work days (not weekends). Currently if I input 5/13/10 as the request date & the due date is in 2 days - the due date shows up as being on 5/15 & I do not work on Saturday or Sunday so I want the due date to be 5/17. Can you help? THANKS!
 
Hi,

Is there any way we include saturdays but not sundays and omit any date from a given list (say, list of national holidays)?

Thanks in advance.
 
If it wasn't for the caveat about including Saturdays, you could use the NETWORKDAYS function. Thanksfully, Chip Pearson has developed both a formula and/or UDF that can tackle your situation:

Formula:

http://www.cpearson.com/excel/betternetworkdays.aspx

UDF:

http://www.cpearson.com/excel/BetterWorkday.aspx


I'd recommend using the UDF, as I think it's a litte easier to understand in the workbook.
 
Thank you. I myself should have found the use of Networkdays function :(.

I just tried the formula by Pearson and found it to be more useful. I've to try the UDF as I'm not very comfortable with VBA - have to go slowly.

Thanks a ton.


Murugaraj
 
I toyed with the idea for some more time and came up with this formula. it works just fine.

Requirements first:

1. I need to calculate the sixth working day from the date of receipt of a project. The sixth working day will be the due date.

2.Saturday are working days for most Indian companies; so is for mine.

3. Holidays for the year 2011 are given in the named range Holidays2011.


Now the formula:

=IF(OR(WEEKDAY(A1)=2,WEEKDAY(A1)=7),WORKDAY(A1,4,Holidays2011)+1,WORKDAY(A1,4,Holidays2011)).

where A1 contains the date from which the due date needs to be calculated.


The logic:

1. If I receive the project on a Monday, I go to the Friday of that week and add one day to get Saturday.

2. If I receive the project on a Saturday, I go to the next Friday, which is nothing but going to the next Thursday and adding one day to get Friday. (Why I'm doing this? Please wait).

3. If I receive the project on any other day, I just move forward five working days from tomorrow.


Caveats:

1. Why I used 4 in the Workday formula, not 6? Because the formula will start counting from the next working day.

2. What is the logic in Logic 2 above? Just to have the same formula as in Logic 1 and to simplify the formula.
 
@murugaraj

Awesome formula, thanks for posting back with what finally worked! =)
 
You must log in or register to reply here.
Back
Top