I have a google sheet that gives me a dynamic day increment. The formula I am using right now is this:
=IF(ISTEXT(A1),1,IF(WEEKDAY(B2)=7,,IF(WEEKDAY(B2)=1,,INDEX(FILTER($A$1:A2,$A$1:A2<>""),COUNT(FILTER($A$1:A2,$A$1:A2<>"")))+1)))
The first part (ISTEXT) checks the cell above to see if it has text...which happens to be the column header. If it does, we start the count at 1. After that, it will increment +1 as long as the date in column B is not a weekend (Saturday or Sunday). If it is, it will leave it blank. Then once it hits Monday again, it continues the count where it left off.
This gives me a dynamic way to count out a 20 work day schedule. I can plug in the start date and it will count out a 20 day work day schedule, skipping weekends. I am trying to add an additional mechanism to incorporate other interruptions to the work day schedule, such as holidays. I have a column (F) that I place notes in. I would like to have something like this incorporated in the original formula:
=if(F2="Holiday","H",<do the other stuff>)
Problem I am having is that everything I did in this original formula is based on empty spaces. Once an H gets placed in the field, it breaks the incrementing. I am thinking maybe I am overcomplicating this or doing something wrong, but hopefully someone can help me out here.
Example: Google Sheets
I duplicated the sheet (see tab JPV_HELP) and entered in B3
=sequence(eomonth(B2, 0)-B2, 1, B2+1, 1)
and in A2
=Arrayformula(if(C2:C<>"", regexreplace(C2:C, "[^A-Z]",), if( (weekday(B2:B) = 1)+(weekday(B2:B) = 7), ,countifs (weekday(B2:B), ">1", weekday(B2:B), "<7", C2:C, "", row(B2:B), "<="&row(B2:B)))))
If anything is entered in column C, the formula will extract the capital letters to output in column A.
See if that works for you?