Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add days, excluding Sunday's and Holiday's
I have a process that requires 7 days to complete. So, I've been asked to add
7 days to a manual inputted date (cell c1), to include Saturday's, but not Sunday's or holiday's. How can I do this? Please help or if you need clarification. Thank you! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add days, excluding Sunday's and Holiday's
Have a look at the WORKDAY function
Example A1 holds today's date (July 8), B1 holds number 5 WORKDAY(A1,B1) returns July 13 (Wed to Wed) You may need to format the cell holding the formula if it returns a numbers like 40009 best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "JoeL" wrote in message ... I have a process that requires 7 days to complete. So, I've been asked to add 7 days to a manual inputted date (cell c1), to include Saturday's, but not Sunday's or holiday's. How can I do this? Please help or if you need clarification. Thank you! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add days, excluding Sunday's and Holiday's
Hi,
Try this courtesy of Bob Philips =A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})* ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(Days) *10))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))) ,ABS(7)) Start date in a1 and a named range called 'Holidays' for any holidays you want to excelude. Format as date. Mike "JoeL" wrote: I have a process that requires 7 days to complete. So, I've been asked to add 7 days to a manual inputted date (cell c1), to include Saturday's, but not Sunday's or holiday's. How can I do this? Please help or if you need clarification. Thank you! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add days, excluding Sunday's and Holiday's
Hi,
I think this is a lot more complicated than a simple WORKDAY function, because of the exclusing of SUNDAYs but not SATURDAYS. The holiday's part is easy. If the process is only 7 days or less then =WORKDAY(A1,A2+1,C1:C5) If not ... Also, the WORKDAY function is an ATP function so in 2003 or earlier you will need to attach it - Tools, Add-ins, check Analysis ToolPak. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "JoeL" wrote: I have a process that requires 7 days to complete. So, I've been asked to add 7 days to a manual inputted date (cell c1), to include Saturday's, but not Sunday's or holiday's. How can I do this? Please help or if you need clarification. Thank you! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add days, excluding Sunday's and Holiday's
Hi Mike,
What's "Days" referencing? -- Kind regards, Niek Otten Microsoft MVP - Excel "Mike H" wrote in message ... Hi, Try this courtesy of Bob Philips =A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})* ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(Days) *10))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))) ,ABS(7)) Start date in a1 and a named range called 'Holidays' for any holidays you want to excelude. Format as date. Mike "JoeL" wrote: I have a process that requires 7 days to complete. So, I've been asked to add 7 days to a manual inputted date (cell c1), to include Saturday's, but not Sunday's or holiday's. How can I do this? Please help or if you need clarification. Thank you! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add days, excluding Sunday's and Holiday's
What you could do is use Chip Pearson's betternetworkdays Function and use
Goal Seek to get 7 as the number of workdays Look he http://www.cpearson.com/excel/betternetworkdays.aspx -- Kind regards, Niek Otten Microsoft MVP - Excel "Shane Devenshire" wrote in message ... Hi, I think this is a lot more complicated than a simple WORKDAY function, because of the exclusing of SUNDAYs but not SATURDAYS. The holiday's part is easy. If the process is only 7 days or less then =WORKDAY(A1,A2+1,C1:C5) If not ... Also, the WORKDAY function is an ATP function so in 2003 or earlier you will need to attach it - Tools, Add-ins, check Analysis ToolPak. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "JoeL" wrote: I have a process that requires 7 days to complete. So, I've been asked to add 7 days to a manual inputted date (cell c1), to include Saturday's, but not Sunday's or holiday's. How can I do this? Please help or if you need clarification. Thank you! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add days, excluding Sunday's and Holiday's
Niek,
I messed up days is a named range containg the days to add, I changed it in the first part of the formula to 7 (the days to add) but forgot in the second but because I had that named range in my sheet it worked for me. It should be =A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})* ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(7)*10 ))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))),AB S(7)) Since then i've been working on a shorter version but cant get it into 1 cell (yet) A1 =start date A2 = =WORKDAY(A1,7,Holidays) A3 = =A2-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=7)) It works but as I said can't get it into a single cell Mike Mike "Niek Otten" wrote: Hi Mike, What's "Days" referencing? -- Kind regards, Niek Otten Microsoft MVP - Excel "Mike H" wrote in message ... Hi, Try this courtesy of Bob Philips =A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})* ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(Days) *10))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))) ,ABS(7)) Start date in a1 and a named range called 'Holidays' for any holidays you want to excelude. Format as date. Mike "JoeL" wrote: I have a process that requires 7 days to complete. So, I've been asked to add 7 days to a manual inputted date (cell c1), to include Saturday's, but not Sunday's or holiday's. How can I do this? Please help or if you need clarification. Thank you! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add days, excluding Sunday's and Holiday's
This is great, but it doesn't include Saturdays. Can we modify it to include
this day too? "Bernard Liengme" wrote: Have a look at the WORKDAY function Example A1 holds today's date (July 8), B1 holds number 5 WORKDAY(A1,B1) returns July 13 (Wed to Wed) You may need to format the cell holding the formula if it returns a numbers like 40009 best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "JoeL" wrote in message ... I have a process that requires 7 days to complete. So, I've been asked to add 7 days to a manual inputted date (cell c1), to include Saturday's, but not Sunday's or holiday's. How can I do this? Please help or if you need clarification. Thank you! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add days, excluding Sunday's and Holiday's
Wow, this is extensive. I didn't get it to work for me though. I changed the
A1's to C7 (input date) and created a list of holidays (named Holidays and formatted to date). However, I keep getting the #NUM! error. "Mike H" wrote: Hi, Try this courtesy of Bob Philips =A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})* ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(Days) *10))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))) ,ABS(7)) Start date in a1 and a named range called 'Holidays' for any holidays you want to excelude. Format as date. Mike "JoeL" wrote: I have a process that requires 7 days to complete. So, I've been asked to add 7 days to a manual inputted date (cell c1), to include Saturday's, but not Sunday's or holiday's. How can I do this? Please help or if you need clarification. Thank you! |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add days, excluding Sunday's and Holiday's
This didn't work for me. It is only 7 days or less, but what are you assuming
is in cells A1, A2 and C1 through C5? Can you break this down for me and maybe I can piece together? Thanks. "Shane Devenshire" wrote: Hi, I think this is a lot more complicated than a simple WORKDAY function, because of the exclusing of SUNDAYs but not SATURDAYS. The holiday's part is easy. If the process is only 7 days or less then =WORKDAY(A1,A2+1,C1:C5) If not ... Also, the WORKDAY function is an ATP function so in 2003 or earlier you will need to attach it - Tools, Add-ins, check Analysis ToolPak. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "JoeL" wrote: I have a process that requires 7 days to complete. So, I've been asked to add 7 days to a manual inputted date (cell c1), to include Saturday's, but not Sunday's or holiday's. How can I do this? Please help or if you need clarification. Thank you! |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add days, excluding Sunday's and Holiday's
Yes, Your long formula works! It has to be array-entered.
(That is, instead of committing with ENTER, commit with CTRL-SHIFT-ENTER) -- Kind regards, Niek Otten Microsoft MVP - Excel "Mike H" wrote in message ... Niek, I messed up days is a named range containg the days to add, I changed it in the first part of the formula to 7 (the days to add) but forgot in the second but because I had that named range in my sheet it worked for me. It should be =A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})* ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(7)*10 ))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))),AB S(7)) Since then i've been working on a shorter version but cant get it into 1 cell (yet) A1 =start date A2 = =WORKDAY(A1,7,Holidays) A3 = =A2-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=7)) It works but as I said can't get it into a single cell Mike Mike "Niek Otten" wrote: Hi Mike, What's "Days" referencing? -- Kind regards, Niek Otten Microsoft MVP - Excel "Mike H" wrote in message ... Hi, Try this courtesy of Bob Philips =A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})* ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(Days) *10))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))) ,ABS(7)) Start date in a1 and a named range called 'Holidays' for any holidays you want to excelude. Format as date. Mike "JoeL" wrote: I have a process that requires 7 days to complete. So, I've been asked to add 7 days to a manual inputted date (cell c1), to include Saturday's, but not Sunday's or holiday's. How can I do this? Please help or if you need clarification. Thank you! |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add days, excluding Sunday's and Holiday's
I assumed you would know that
"Niek Otten" wrote: Yes, Your long formula works! It has to be array-entered. (That is, instead of committing with ENTER, commit with CTRL-SHIFT-ENTER) -- Kind regards, Niek Otten Microsoft MVP - Excel "Mike H" wrote in message ... Niek, I messed up days is a named range containg the days to add, I changed it in the first part of the formula to 7 (the days to add) but forgot in the second but because I had that named range in my sheet it worked for me. It should be =A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})* ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(7)*10 ))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))),AB S(7)) Since then i've been working on a shorter version but cant get it into 1 cell (yet) A1 =start date A2 = =WORKDAY(A1,7,Holidays) A3 = =A2-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=7)) It works but as I said can't get it into a single cell Mike Mike "Niek Otten" wrote: Hi Mike, What's "Days" referencing? -- Kind regards, Niek Otten Microsoft MVP - Excel "Mike H" wrote in message ... Hi, Try this courtesy of Bob Philips =A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})* ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(Days) *10))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))) ,ABS(7)) Start date in a1 and a named range called 'Holidays' for any holidays you want to excelude. Format as date. Mike "JoeL" wrote: I have a process that requires 7 days to complete. So, I've been asked to add 7 days to a manual inputted date (cell c1), to include Saturday's, but not Sunday's or holiday's. How can I do this? Please help or if you need clarification. Thank you! |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add days, excluding Sunday's and Holiday's
Instead of ENTER, use CTRL+SHIFT+ENTER to commit the formula
-- Kind regards, Niek Otten Microsoft MVP - Excel "JoeL" wrote in message ... Wow, this is extensive. I didn't get it to work for me though. I changed the A1's to C7 (input date) and created a list of holidays (named Holidays and formatted to date). However, I keep getting the #NUM! error. "Mike H" wrote: Hi, Try this courtesy of Bob Philips =A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})* ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(Days) *10))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))) ,ABS(7)) Start date in a1 and a named range called 'Holidays' for any holidays you want to excelude. Format as date. Mike "JoeL" wrote: I have a process that requires 7 days to complete. So, I've been asked to add 7 days to a manual inputted date (cell c1), to include Saturday's, but not Sunday's or holiday's. How can I do this? Please help or if you need clarification. Thank you! |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add days, excluding Sunday's and Holiday's
I didn't know that... but now this formula works great. Thank you!
"Mike H" wrote: I assumed you would know that "Niek Otten" wrote: Yes, Your long formula works! It has to be array-entered. (That is, instead of committing with ENTER, commit with CTRL-SHIFT-ENTER) -- Kind regards, Niek Otten Microsoft MVP - Excel "Mike H" wrote in message ... Niek, I messed up days is a named range containg the days to add, I changed it in the first part of the formula to 7 (the days to add) but forgot in the second but because I had that named range in my sheet it worked for me. It should be =A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})* ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(7)*10 ))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))),AB S(7)) Since then i've been working on a shorter version but cant get it into 1 cell (yet) A1 =start date A2 = =WORKDAY(A1,7,Holidays) A3 = =A2-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=7)) It works but as I said can't get it into a single cell Mike Mike "Niek Otten" wrote: Hi Mike, What's "Days" referencing? -- Kind regards, Niek Otten Microsoft MVP - Excel "Mike H" wrote in message ... Hi, Try this courtesy of Bob Philips =A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})* ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(Days) *10))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))) ,ABS(7)) Start date in a1 and a named range called 'Holidays' for any holidays you want to excelude. Format as date. Mike "JoeL" wrote: I have a process that requires 7 days to complete. So, I've been asked to add 7 days to a manual inputted date (cell c1), to include Saturday's, but not Sunday's or holiday's. How can I do this? Please help or if you need clarification. Thank you! |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add days, excluding Sunday's and Holiday's
Your welcome and I should have pointed out it was an array
"JoeL" wrote: I didn't know that... but now this formula works great. Thank you! "Mike H" wrote: I assumed you would know that "Niek Otten" wrote: Yes, Your long formula works! It has to be array-entered. (That is, instead of committing with ENTER, commit with CTRL-SHIFT-ENTER) -- Kind regards, Niek Otten Microsoft MVP - Excel "Mike H" wrote in message ... Niek, I messed up days is a named range containg the days to add, I changed it in the first part of the formula to 7 (the days to add) but forgot in the second but because I had that named range in my sheet it worked for me. It should be =A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})* ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(7)*10 ))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))),AB S(7)) Since then i've been working on a shorter version but cant get it into 1 cell (yet) A1 =start date A2 = =WORKDAY(A1,7,Holidays) A3 = =A2-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=7)) It works but as I said can't get it into a single cell Mike Mike "Niek Otten" wrote: Hi Mike, What's "Days" referencing? -- Kind regards, Niek Otten Microsoft MVP - Excel "Mike H" wrote in message ... Hi, Try this courtesy of Bob Philips =A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})* ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(Days) *10))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))) ,ABS(7)) Start date in a1 and a named range called 'Holidays' for any holidays you want to excelude. Format as date. Mike "JoeL" wrote: I have a process that requires 7 days to complete. So, I've been asked to add 7 days to a manual inputted date (cell c1), to include Saturday's, but not Sunday's or holiday's. How can I do this? Please help or if you need clarification. Thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting # of days between 2 dates excluding Fri & Sat) | Excel Worksheet Functions | |||
Excluding leap days in a formula | Excel Discussion (Misc queries) | |||
Working Days excluding Sundays | Excel Discussion (Misc queries) | |||
Due date excluding weekend days | Excel Worksheet Functions | |||
Count Days excluding Sundays | Excel Worksheet Functions |