Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate the number of days to exclude Sunday & Holidays
I need to know what the third business day from a Manually entered date in
Cell B37 to exclude Sundays and Holidays. So far I have: =WORKDAY(B37,3,Holidays) But I realized that I need it include Sat. and this formula won't do that. Any suggestions? THANKS! -- -Liz |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate the number of days to exclude Sunday & Holidays
Hi,
Try this ARRAY formula. Holidays is a named range that you enter holiday dates in. This adds the number of days in B1 but if you want you can change every instance of B1 to a 3 (4 of them) =B37+SMALL(IF(WEEKDAY(A1+ROW(INDIRECT("1:"&B1*10)) )<1,IF(ISNA(MATCH(A1+ROW(INDIRECT("1:"&B1*10)), Holidays,0)),ROW(INDIRECT("1:"&B1*10)))),B1) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "LSG" wrote: I need to know what the third business day from a Manually entered date in Cell B37 to exclude Sundays and Holidays. So far I have: =WORKDAY(B37,3,Holidays) But I realized that I need it include Sat. and this formula won't do that. Any suggestions? THANKS! -- -Liz |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate the number of days to exclude Sunday & Holidays
oops,
there's an error in the last formula, i wrote it for A1 then changed it to B37 to meet your needs and forgot to change a reference =B37+SMALL(IF(WEEKDAY(B37+ROW(INDIRECT("1:"&B1*10) ))<1,IF(ISNA(MATCH(B37+ROW(INDIRECT("1:"&B1*10)), Holidays,0)),ROW(INDIRECT("1:"&B1*10)))),B1) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Hi, Try this ARRAY formula. Holidays is a named range that you enter holiday dates in. This adds the number of days in B1 but if you want you can change every instance of B1 to a 3 (4 of them) =B37+SMALL(IF(WEEKDAY(A1+ROW(INDIRECT("1:"&B1*10)) )<1,IF(ISNA(MATCH(A1+ROW(INDIRECT("1:"&B1*10)), Holidays,0)),ROW(INDIRECT("1:"&B1*10)))),B1) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "LSG" wrote: I need to know what the third business day from a Manually entered date in Cell B37 to exclude Sundays and Holidays. So far I have: =WORKDAY(B37,3,Holidays) But I realized that I need it include Sat. and this formula won't do that. Any suggestions? THANKS! -- -Liz |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate the number of days to exclude Sunday & Holidays
thanks for the formula, but I'm getting an error message. What is 'B1'
suppose to be? -- -Liz "Mike H" wrote: oops, there's an error in the last formula, i wrote it for A1 then changed it to B37 to meet your needs and forgot to change a reference =B37+SMALL(IF(WEEKDAY(B37+ROW(INDIRECT("1:"&B1*10) ))<1,IF(ISNA(MATCH(B37+ROW(INDIRECT("1:"&B1*10)), Holidays,0)),ROW(INDIRECT("1:"&B1*10)))),B1) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Hi, Try this ARRAY formula. Holidays is a named range that you enter holiday dates in. This adds the number of days in B1 but if you want you can change every instance of B1 to a 3 (4 of them) =B37+SMALL(IF(WEEKDAY(A1+ROW(INDIRECT("1:"&B1*10)) )<1,IF(ISNA(MATCH(A1+ROW(INDIRECT("1:"&B1*10)), Holidays,0)),ROW(INDIRECT("1:"&B1*10)))),B1) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "LSG" wrote: I need to know what the third business day from a Manually entered date in Cell B37 to exclude Sundays and Holidays. So far I have: =WORKDAY(B37,3,Holidays) But I realized that I need it include Sat. and this formula won't do that. Any suggestions? THANKS! -- -Liz |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate the number of days to exclude Sunday & Holidays
thanks for the formula, but I'm getting an error message. What is 'B1'
suppose to be? From My first post Try this ARRAY formula. Holidays is a named range that you enter holiday dates in. This adds the number of days in B1 but if you want you can change every instance of B1 to a 3 (4 of them) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "LSG" wrote: thanks for the formula, but I'm getting an error message. What is 'B1' suppose to be? -- -Liz "Mike H" wrote: oops, there's an error in the last formula, i wrote it for A1 then changed it to B37 to meet your needs and forgot to change a reference =B37+SMALL(IF(WEEKDAY(B37+ROW(INDIRECT("1:"&B1*10) ))<1,IF(ISNA(MATCH(B37+ROW(INDIRECT("1:"&B1*10)), Holidays,0)),ROW(INDIRECT("1:"&B1*10)))),B1) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Hi, Try this ARRAY formula. Holidays is a named range that you enter holiday dates in. This adds the number of days in B1 but if you want you can change every instance of B1 to a 3 (4 of them) =B37+SMALL(IF(WEEKDAY(A1+ROW(INDIRECT("1:"&B1*10)) )<1,IF(ISNA(MATCH(A1+ROW(INDIRECT("1:"&B1*10)), Holidays,0)),ROW(INDIRECT("1:"&B1*10)))),B1) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "LSG" wrote: I need to know what the third business day from a Manually entered date in Cell B37 to exclude Sundays and Holidays. So far I have: =WORKDAY(B37,3,Holidays) But I realized that I need it include Sat. and this formula won't do that. Any suggestions? THANKS! -- -Liz |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate the number of days to exclude Sunday & Holidays
Oops, sorry I had misread that. But it works now! thanks!
-- -Liz "Mike H" wrote: thanks for the formula, but I'm getting an error message. What is 'B1' suppose to be? From My first post Try this ARRAY formula. Holidays is a named range that you enter holiday dates in. This adds the number of days in B1 but if you want you can change every instance of B1 to a 3 (4 of them) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "LSG" wrote: thanks for the formula, but I'm getting an error message. What is 'B1' suppose to be? -- -Liz "Mike H" wrote: oops, there's an error in the last formula, i wrote it for A1 then changed it to B37 to meet your needs and forgot to change a reference =B37+SMALL(IF(WEEKDAY(B37+ROW(INDIRECT("1:"&B1*10) ))<1,IF(ISNA(MATCH(B37+ROW(INDIRECT("1:"&B1*10)), Holidays,0)),ROW(INDIRECT("1:"&B1*10)))),B1) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Hi, Try this ARRAY formula. Holidays is a named range that you enter holiday dates in. This adds the number of days in B1 but if you want you can change every instance of B1 to a 3 (4 of them) =B37+SMALL(IF(WEEKDAY(A1+ROW(INDIRECT("1:"&B1*10)) )<1,IF(ISNA(MATCH(A1+ROW(INDIRECT("1:"&B1*10)), Holidays,0)),ROW(INDIRECT("1:"&B1*10)))),B1) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "LSG" wrote: I need to know what the third business day from a Manually entered date in Cell B37 to exclude Sundays and Holidays. So far I have: =WORKDAY(B37,3,Holidays) But I realized that I need it include Sat. and this formula won't do that. Any suggestions? THANKS! -- -Liz |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate the number of days to exclude Sunday & Holidays
Here's another one...
Create these defined names... InsertNameDefine Name: Array Refers to: ={1;2;3;4;5;6;7;8;9;10} Name: Days Refers to: ={2,3,4,5,6,7} OK out A1 = some date B1 = the number of workdays* = 3 Holidays = range of dates to be excluded Then, array entered** : =A1+1*SMALL(IF(WEEKDAY(A1+1*Array)=Days*ISNA(MATCH (A1+1*Array,Holidays,0)),Array),B1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. * this formula is specifically written to calculate *future dates* so the number of workdays must be a positive number. -- Biff Microsoft Excel MVP "LSG" wrote in message ... I need to know what the third business day from a Manually entered date in Cell B37 to exclude Sundays and Holidays. So far I have: =WORKDAY(B37,3,Holidays) But I realized that I need it include Sat. and this formula won't do that. Any suggestions? THANKS! -- -Liz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exclude Sunday & Holidays | Excel Discussion (Misc queries) | |||
Calculate the number of working days minus holidays (Canadian) | New Users to Excel | |||
Add days to a date, but exclude holidays | Excel Worksheet Functions | |||
Number of Week Days _including_ Holidays | Excel Worksheet Functions | |||
formula to calculate # of days between dates, excluding holidays | Excel Discussion (Misc queries) |