Forum Discussion
IF / Countif / Sumif - Formula help please
I can't get this if function to do what I want. What am I doing wrong?
Column C. I'm trying to get the column to add one for every row through the number at the top of the sheet (20, 26 etc) excluding weekends and holidays.
For example:
Row 2 defined ID (5)
1/1/2017 Holiday <----
1/2/2017 1
1/3/2017 2
1/4/2017 3
7/4/2017 Holiday <----skips counting this day due to holiday
7/5/2017 4
7/6/2017 5
7/7/2017 1 <-- restarts based on row 2
etc....
Count each day that is not a weekend or holiday and then return the count to 1 when it is equal to or less than the amount defined by the top row.
Any help is appreciated.
9 Replies
Have you looked at the NETWORKDAYS or NETWORKDAYS.INTL functions?
Hi Steve,
That could be, but the problem with NETWORKDAYS.INTL it's not possible to mark weekend as workday. For example, if we have regular weekends on Saturday only and government decides one time in the year we work on Sat but be off on Sun instead - we may add such Sun to holidays list, but we can't say to function what this concrete Sat is not weekend.
But perhaps that's not Chris' case, he considers fixed not more than 5 working days cycle.
Example data and formula
=IF(AND($C12+1<=$C$2,IF($B13="",$C12+1)),IF(AND(C12=0,C11=0),C10+1),C12+1)
util 1 util 2 20 26 READ DATE HOLIDAY/WEEKEND CYCLE Sunday, January 01, 2017 YES Monday, January 02, 2017 YES Tuesday, January 03, 2017 1 1 Wednesday, January 04, 2017 2 2 Thursday, January 05, 2017 3 3 Friday, January 06, 2017 4 4 Saturday, January 07, 2017 YES 0 0 Sunday, January 08, 2017 YES 0 0 Monday, January 09, 2017 5 5 Tuesday, January 10, 2017 FALSE 6 Wednesday, January 11, 2017 FALSE 7 Thursday, January 12, 2017 FALSE 8 Friday, January 13, 2017 FALSE 9 Saturday, January 14, 2017 YES 1 0 Sunday, January 15, 2017 YES 2 0 Monday, January 16, 2017 FALSE 10 Tuesday, January 17, 2017 FALSE 11 Wednesday, January 18, 2017 FALSE 12 Further to this...on my sample data below. The column with "Util 2 (26)" header example is how I'm trying to get column "Util 1 (20)" to look like. Note how the count of dates continually increment up except for weekends and holidays until it gets to top number where I want it to start over.
Thanks
Hi Chris,
In attached file is the prototype, results looks like
Instead of 26 you may put any number greater than 3 (number of rows above your data).
Calculations are added into the column F in parallel to your data where i added one more YES in yellow. Here is an assumption - you have to have the size of your cycle (26) somewhere in first rows of the column F, otherwise the logic of the formula is to be changed.
To generate the counter i used
=ROUND(MOD(<prev value>+0.7,26),0)
0.7 here is any one between 0.5 and 1 to round the result to next integer, you may use ROUNDUP as well.
To find the previous value for the counter we check for the current cell
OFFSET(F4,-1,0)
If it's not zero we take that value, if zero we find the latest non-zero value in the upper region with max 26 height size, or reduce the size if the current row is less than 26 to avoid the error generation. This part of the formula finds in reverse order the position of such value in above region
INDEX(MATCH(2,(--1/OFFSET(F4,-MIN(ROW()-1,$F$2),0,MIN(ROW()-1,$F$2))<>0))
After that we offset on this position and pickup the value of the previous counter
OFFSET(F4, -MIN(ROW(),$F$2)-(ROW()>$F$2)+INDEX(MATCH(2,(--1/OFFSET(F4,-MIN(ROW()-1,$F$2),0,MIN(ROW()-1,$F$2))<>0)),) ,0)
MIN gives the size of the region depends on current row position.
The correction
-(ROW()>$F$2)
is to add one more position when row number begins bigger than counter size. Perhaps could be done another way, but i didn't dig.
Entire formula for F4 is
=IF(B4="YES", 0, ROUND(MOD( IF(OFFSET(F4,-1,0)=0, OFFSET(F4, -MIN(ROW(),$F$2)-(ROW()>$F$2)+INDEX(MATCH(2,--(1/OFFSET(F4,-MIN(ROW()-1,$F$2),0,MIN(ROW()-1,$F$2))<>0)),),0), OFFSET(F4,-1,0) )+0.7, $F$2 ),0 ) )copy it down to another cells. And finally apply custom format to them
0;;;@,
which shows zeroes as empty cells and the rest as integers.
Hi Chris,
Will try to play tomorrow if no one answers before. What 20 and 26 means under util 1 and util 2?
Thank you. Much appreciated.
Will try to play tomorrow if no one answers before. What 20 and 26 means under util 1 and util 2?
The 20 or 26 is the number to that the count should reach before starting back at 1.
Does that make sense?
