| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ignoring weekends & holidays-NETWORKDAYS?
A couple of great people here helped me with a minor prob I had on this one & then my boss threw something else into the mix that has me stumped: The scenario below works great, but the end date appears 3 months late because it includes holidays & weekends. I thought NETWORKDAYS would help, but I think I am mistaken. I have a schedule of holiday dates through year 2010 off to the side in cells N8-S18 that a workable formula could refer to. Any suggestions will be duly praised and appreciated! ........C...................D..................... ......E......... 1......1............10/25/2005...........10/26/2005 2......2............10/27/2005...........10/29/2005 3......0............10/29/2005...........10/29/2005 4......3............10/29/2005...........11/01/2005 Column C is the duration of the project in days. Column D is the project start date. Column E is the project end date. As example, in D4 I'm using the formula =IF(C3=0,E3+0,E3+1). I need to include something to make it ignore weekends & holidays. I don't have much experience in multiple conditions - I tried some things & made a decent mess of it. -- chaminod ------------------------------------------------------------------------ chaminod's Profile: http://www.excelforum.com/member.php...o&userid=18163 View this thread: http://www.excelforum.com/showthread...hreadid=493488 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ignoring weekends & holidays-NETWORKDAYS?
NETWORKDAYS ignores weekend and holidays (holidays need to be input in the
formula) -- Regards, Peo Sjoblom "chaminod" wrote in message ... A couple of great people here helped me with a minor prob I had on this one & then my boss threw something else into the mix that has me stumped: The scenario below works great, but the end date appears 3 months late because it includes holidays & weekends. I thought NETWORKDAYS would help, but I think I am mistaken. I have a schedule of holiday dates through year 2010 off to the side in cells N8-S18 that a workable formula could refer to. Any suggestions will be duly praised and appreciated! .......C...................D..................... ......E......... 1......1............10/25/2005...........10/26/2005 2......2............10/27/2005...........10/29/2005 3......0............10/29/2005...........10/29/2005 4......3............10/29/2005...........11/01/2005 Column C is the duration of the project in days. Column D is the project start date. Column E is the project end date. As example, in D4 I'm using the formula =IF(C3=0,E3+0,E3+1). I need to include something to make it ignore weekends & holidays. I don't have much experience in multiple conditions - I tried some things & made a decent mess of it. -- chaminod ------------------------------------------------------------------------ chaminod's Profile: http://www.excelforum.com/member.php...o&userid=18163 View this thread: http://www.excelforum.com/showthread...hreadid=493488 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ignoring weekends & holidays-NETWORKDAYS?
Glad to hear I actually was on the right track with NETWORKDAYS, but how do I incorporate NETWORKDAYS into my =IF formula? It needs to consider both of those conditions - the fact that weekends & holidays don't count and whether or not to add the 1 day to the prior date. Thanks so much for responding - I really appreciate it. -- chaminod ------------------------------------------------------------------------ chaminod's Profile: http://www.excelforum.com/member.php...o&userid=18163 View this thread: http://www.excelforum.com/showthread...hreadid=493488 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ignoring weekends & holidays-NETWORKDAYS?
I don't totally understand your question / scenario but both the NETWORKDAYS
and WORKDAY functions exclude weekends. They also both have optional arguments for you to enter a range that has your companies holiday dates in them. This should get you what you want. WORKDAY(1/1/05, 5, 1/3/05) will return 1/10/05 because 1/1 and 1/2 are a weekend and 1/3 is listed as a holiday. 5 working days from 1/1 = 1/10. NETWORKDAYS(1/1/05, 1/10/05, 1/3/05) will return 5 because 1/1, 1/2, 1/8, and 1/9 are weekends and because 1/3 is listed as a holiday. 5 working days between the 2 dates. Hope this helps. Thanks, Bill Horton "chaminod" wrote: A couple of great people here helped me with a minor prob I had on this one & then my boss threw something else into the mix that has me stumped: The scenario below works great, but the end date appears 3 months late because it includes holidays & weekends. I thought NETWORKDAYS would help, but I think I am mistaken. I have a schedule of holiday dates through year 2010 off to the side in cells N8-S18 that a workable formula could refer to. Any suggestions will be duly praised and appreciated! ........C...................D..................... ......E......... 1......1............10/25/2005...........10/26/2005 2......2............10/27/2005...........10/29/2005 3......0............10/29/2005...........10/29/2005 4......3............10/29/2005...........11/01/2005 Column C is the duration of the project in days. Column D is the project start date. Column E is the project end date. As example, in D4 I'm using the formula =IF(C3=0,E3+0,E3+1). I need to include something to make it ignore weekends & holidays. I don't have much experience in multiple conditions - I tried some things & made a decent mess of it. -- chaminod ------------------------------------------------------------------------ chaminod's Profile: http://www.excelforum.com/member.php...o&userid=18163 View this thread: http://www.excelforum.com/showthread...hreadid=493488 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ignoring weekends & holidays-NETWORKDAYS?
Hi,
With a list of dates (holidays) in a1:a12 =NETWORKDAYS(DATEVALUE("12/31/05"),"12/31/06",A1:A12) =252 -- John johnf202 at hot mail dot com "chaminod" wrote in message ... A couple of great people here helped me with a minor prob I had on this one & then my boss threw something else into the mix that has me stumped: The scenario below works great, but the end date appears 3 months late because it includes holidays & weekends. I thought NETWORKDAYS would help, but I think I am mistaken. I have a schedule of holiday dates through year 2010 off to the side in cells N8-S18 that a workable formula could refer to. Any suggestions will be duly praised and appreciated! .......C...................D..................... ......E......... 1......1............10/25/2005...........10/26/2005 2......2............10/27/2005...........10/29/2005 3......0............10/29/2005...........10/29/2005 4......3............10/29/2005...........11/01/2005 Column C is the duration of the project in days. Column D is the project start date. Column E is the project end date. As example, in D4 I'm using the formula =IF(C3=0,E3+0,E3+1). I need to include something to make it ignore weekends & holidays. I don't have much experience in multiple conditions - I tried some things & made a decent mess of it. -- chaminod ------------------------------------------------------------------------ chaminod's Profile: http://www.excelforum.com/member.php...o&userid=18163 View this thread: http://www.excelforum.com/showthread...hreadid=493488 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ignoring weekends & holidays-NETWORKDAYS?
Instead of listing all holidays in the parens, I tried, per instructions,
listing them in individual cells, and selecting those cells, eg, !HolidayA1:A11 The big problem is I have a long column of dates where I want to use the WORKDAY function, but when I copy and paste the formula, or drag it, all the way down, the A1:A:11 becomes A2:A12 in the next row, etc. I even tried using a $ in front of the A1 and A11 but it didn't help. Help! "William Horton" wrote: I don't totally understand your question / scenario but both the NETWORKDAYS and WORKDAY functions exclude weekends. They also both have optional arguments for you to enter a range that has your companies holiday dates in them. This should get you what you want. WORKDAY(1/1/05, 5, 1/3/05) will return 1/10/05 because 1/1 and 1/2 are a weekend and 1/3 is listed as a holiday. 5 working days from 1/1 = 1/10. NETWORKDAYS(1/1/05, 1/10/05, 1/3/05) will return 5 because 1/1, 1/2, 1/8, and 1/9 are weekends and because 1/3 is listed as a holiday. 5 working days between the 2 dates. Hope this helps. Thanks, Bill Horton "chaminod" wrote: A couple of great people here helped me with a minor prob I had on this one & then my boss threw something else into the mix that has me stumped: The scenario below works great, but the end date appears 3 months late because it includes holidays & weekends. I thought NETWORKDAYS would help, but I think I am mistaken. I have a schedule of holiday dates through year 2010 off to the side in cells N8-S18 that a workable formula could refer to. Any suggestions will be duly praised and appreciated! ........C...................D..................... ......E......... 1......1............10/25/2005...........10/26/2005 2......2............10/27/2005...........10/29/2005 3......0............10/29/2005...........10/29/2005 4......3............10/29/2005...........11/01/2005 Column C is the duration of the project in days. Column D is the project start date. Column E is the project end date. As example, in D4 I'm using the formula =IF(C3=0,E3+0,E3+1). I need to include something to make it ignore weekends & holidays. I don't have much experience in multiple conditions - I tried some things & made a decent mess of it. -- chaminod ------------------------------------------------------------------------ chaminod's Profile: http://www.excelforum.com/member.php...o&userid=18163 View this thread: http://www.excelforum.com/showthread...hreadid=493488 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ignoring weekends & holidays-NETWORKDAYS?
You're on the right track If you want to drag down and keep the refs the same use A$1:A$11 -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=493488 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ignoring weekends & holidays-NETWORKDAYS?
Thank you, thank you, daddy, I was close but not quite there...much
appreciated! What I'm really trying to do is enforce a contract which required delivery of a circuit in "40 calendar days, but when I used "date + 40" formula it landed on weekends. Of course, now that I"m using "workday,40,A1:A12), it's coming up with a lot MORE than 40 calendar days, so I"m suing "Workday,29,A1:A:11). Might there be a formula that "calculates X calendar days after a selected start date, excluding, but not adding extra days for, weekends and holidays"? "daddylonglegs" wrote: You're on the right track If you want to drag down and keep the refs the same use A$1:A$11 -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=493488 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ignoring weekends & holidays-NETWORKDAYS?
If you want to add 40 calendar days to a date in B1, but skip to the next workday if this would give a weekend or holiday date..... =WORKDAY(B1+39,1,A$1:A$11) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=493488 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ignoring weekends & holidays-NETWORKDAYS?
Thank you, that is SOOOO cool! So may I ask how you gathered such expertise?
"daddylonglegs" wrote: If you want to add 40 calendar days to a date in B1, but skip to the next workday if this would give a weekend or holiday date..... =WORKDAY(B1+39,1,A$1:A$11) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=493488 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do you count work days excluding weekends and holidays? | Excel Discussion (Misc queries) | |||
| Schedule to exclude weekends and holidays | Excel Discussion (Misc queries) | |||
| Formula - Excluding weekends & holidays | Excel Worksheet Functions | |||
| Calculating days between current date and a date in future NETWORKDAYS() function | Excel Worksheet Functions | |||
| Skip the Holidays 2 | Excel Discussion (Misc queries) | |||
Linear Mode
