 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 20084 day work week
What I need to do is have a project start date and an end date and a duration of days. We work a 4 day work week here and I need to not count Friday (Sat or Sun) in the duration of the projects. I just need a correct end date for the projects. Thanks so much!!! use this to calculate the working days =SUMPRODUCT(INT((MAX(end_date,start_date)-WEEKDAY(MAX(end_date,start_date)+1-{2;3;4;5})-MIN(end_date,start_date)+8)/7)) or this if you want to exclude holidays =SUMPRODUCT(INT((MAX(end_date,start_date)-WEEKDAY(MAX(end_date,start_date)+1-{2;3;4;5})-MIN(end_date,start_date)+8)/7)) -SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4;5},0))*(holidays>=MIN(end_date,start_date))*(holidays<=MAX(end_date,start_date)))  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > What I need to do is have a project start date and an end date and a > duration of days. We work a 4 day work week here and I need to not count > Friday (Sat or Sun) in the duration of the projects. I just need a correct > end date for the projects. > > Thanks so much!!! A1: start date A2: end date =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)<5)) > What I need to do is have a project start date and an end date and a > duration of days. We work a 4 day work week here and I need to not count > Friday (Sat or Sun) in the duration of the projects. I just need a correct > end date for the projects. > > Thanks so much!!! I'm very new at this program so please bear with me...I did type the formula in and it didn't work. I think I am just not putting the right information in the correct fields. The schedule is set up to have A1 to be the start date and B1 the finish date. We have multiple units being built at once. They take 10 days to complete, with a 4-day workweek. If you could walk me through it...that would be so great! And thank you for responding! > A1: start date > A2: end date [quoted text clipped - 7 lines] > > > > Thanks so much!!! If you know the start date, and the end date and how long it takes, what exactly are you trying to calculate? Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > I'm very new at this program so please bear with me...I did type the > formula [quoted text clipped - 19 lines] >> > >> > Thanks so much!!! The end date is what I am trying to get...using a 4 day workweek. > If you know the start date, and the end date and how long it takes, what > exactly are you trying to calculate? [quoted text clipped - 22 lines] > >> > > >> > Thanks so much!!! =start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_date+SIGN(days)*(ROW(INDIRECT("1:"&ABS(days)*10))))={1,2,3,4}),ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days)) Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > The end date is what I am trying to get...using a 4 day workweek. > [quoted text clipped - 28 lines] >> >> > >> >> > Thanks so much!!! Where do I put this information? > =start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_date+SIGN(days)*(ROW(INDIRECT("1:"&ABS(days)*10))))={1,2,3,4}),ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days)) > [quoted text clipped - 30 lines] > >> >> > > >> >> > Thanks so much!!! Anywhere. You just change the variable names to the appropriate cells. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Where do I put this information? > [quoted text clipped - 37 lines] >> >> >> > >> >> >> > Thanks so much!!! Thank you for your time Bob! > Anywhere. You just change the variable names to the appropriate cells. > [quoted text clipped - 39 lines] > >> >> >> > > >> >> >> > Thanks so much!!! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.