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

Weekday Formula required for Planning Sheet

P

Proficient

New Member
Assalam-o-alaikum & Hi to every one, I have a question about weekdays for my planning sheet, I have asked this on another site but did not get any positive response up till now then through Google I found this web and hoping that you guys will help me, I am sending you the link here, kindly check this out and resolve my problem, here is the link


http://www.mrexcel.com/forum/excel-questions/677439-planning-sheet-sundays-holiday-not-required-plan.html


Thanks in advance


Kind Regards,

Kamran Mumtaz
 
Do you have XL 2010? If so, you'll be able to use WORKDAY.INTL function which lets you define custom weekdays. Then all the +/- functions can be converted to something like

WORKDAY.INTL(StartPoint,Days,11,$L$3:$L$16)


PS. Thank you very much for posting link to previous discussion. VERY helpful. =)
 
Sorry I forgot to mention excel version, I am using excel 2007, did you see the question? I know its very complicated but I hope you guys will help me... Thanks


Regards,

Kamran Mumtaz
 
Yes, I was able to read your question. That's how I knew you needed a type of Workday function. =P


Check out this thread. The link to Chip Pearson's sight has a UDF that you can use to replace the WORKDAY.INTL function, which will let you easily calculate a date given a reference date and +/- amount of days.

http://chandoo.org/forums/topic/workdayintl-function-equivalent-in-excel-2007
 
i believe i did something like this a short stent back.... but cant find that spreadsheet.


just to verify, you only want Mon-Fri counted and the Holidays listed to be excluded as well?
 
@jason

I think it's Mon-Sat get counted, and Sunday's and holidays get excluded. If it's just Mon-Fri, things just got a whole lot easier. =)
 
ahh... i see........ i know what i did was M-F. so may formula may not work then.... still going to play around! ;)


so for the first job: the input start date is 1/7/13 and the last ouput date should be 19 days after: 1/29/13? (starting day 1 on 1/7/13, not including any sundays, and not included 1/15/13 due to the holiday)?? is that correct?
 
ok.... this 'APPEARS TO BE WORKING' HAHAHAHA


My layout is the same as the sample in your link. A1=Job, B1=order qty, C1=capacity, D1=required days, E1=Input start date, F1=Input End Days, G1=1st output date, H1=last output date, I1=Dispatch start date, J1=Dispatch End Date. Holidays: I named the range L2:L6 'Holidays'.


E2:=WORKDAY.INTL(H2,-3,11,holidays) RESULT: 1/25/2013

F2:=WORKDAY.INTL(G2,-2,11,holidays) RESULT: 1/4/2013

G2:1/7/2013 (manually entered) RESULT: 1/7/2013

H2:=WORKDAY.INTL(G2,D2,11,holidays)-1 RESULT: 1/29/2013

I2:=WORKDAY.INTL(G2,1,11,holidays) RESULT: 1/8/2013

J2:=WORKDAY.INTL(H2,1,11,holidays) RESULT: 1/30/2013


I have the other rows filled out by dragging the formulas in row1 down with the obviously exception of G2. That cell contains: =WORKDAY.INTL(H2,1,11,holidays)


again... as far as i can see, this appears to be counting saturdays, excluding sundays and the dates listed in the 'Holiday' range.
 
due to my works security restrictions, i can not upload the sample file nor can i email the book to my personal email. I may be able to email my spreadsheet to 'myself' and access my work account from to upload if thats needed but may not be able to accomplish that today/tonight.


hopefully this will work (or if not, hopefully an easy fix can be made to a formula or 2...or 3 or 4 or ...all lol)
 
@jason

Nice job on the formulas, but...WORKDAY.INTL only works in XL2010+, not 2007. =(
 
@Proficient,


If you copy all of the code from the UDF found here:

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


You should then be able to use Jason's formulas, slightly tweaked:

E2:=WORKDAY2(H2,-3,1,holidays) RESULT: 1/25/2013

F2:=WORKDAY2(G2,-2,1,holidays) RESULT: 1/4/2013

G2:1/7/2013 (manually entered) RESULT: 1/7/2013

H2:=WORKDAY2(G2,D2,1,holidays)-1 RESULT: 1/29/2013

I2:=WORKDAY2(G2,1,1,holidays) RESULT: 1/8/2013

J2:=WORKDAY2(H2,1,1,holidays) RESULT: 1/30/2013
 
You must log in or register to reply here.
Back
Top