Help me skip sundays for a work week!

Copper Contributor

This is the formula that came with the template. I need to my days to skip sundays when I enter "no. of days". I need this for a construction timeline but we are not working sundays. What can I put in the formula to exclude those days? Thank you!!

 

=IF(AND($C9="Goal",N$5>=$F9,N$5<=$F9+$G9-1),2,IF(AND($C9="Milestone",N$5>=$F9,N$5<=$F9+$G9-1),1,""))

14 Replies

@DGideon 

I adjusted first part of the formula for Goal. For Milestone it shall be similar, but better to have some data to test.

=IF(WEEKDAY(I$5,2)=7,"",IF(AND($C9="Goal",I$5>=$F9,NETWORKDAYS.INTL($F9,I$5,11)<=$G9),2,IF(AND($C9="Milestone",I$5>=$F9,I$5<=$F9+$G9-1),1,"")))

 

I attached the file to the post. I tried pasting what you provided in the box and it did not fix my issue. It's still counting sunday. Thank you though


@Sergei Baklan wrote:

@DGideon 

I adjusted first part of the formula for Goal. For Milestone it shall be similar, but better to have some data to test.

=IF(WEEKDAY(I$5,2)=7,"",IF(AND($C9="Goal",I$5>=$F9,NETWORKDAYS.INTL($F9,I$5,11)<=$G9),2,IF(AND($C9="Milestone",I$5>=$F9,I$5<=$F9+$G9-1),1,"")))

 



@Sergei Baklan 

@DGideon 

Did you check the file I applied to my previous post? Updated formula is applied to couple of first rows.

Wow! Thank you! That's exactly what I need it to do. Now how can I apply the same formula to the remaining sundays (columns)??


@Sergei Baklan wrote:

@DGideon 

Did you check the file I applied to my previous post? Updated formula is applied to couple of first rows.



@Sergei Baklan 

@DGideon 

Stay on the top left cell (I9) and drag it to the right till end of the time scale

image.png

Next drag entire this row down till end of your range.

Could you provide the same spreadsheet but skipping sat and sunday?

Thankyou @Sergei Baklan 

@k_squared 

At the beginning of the formula above please change

=IF(WEEKDAY(I$5,2)=7,"",IF(

on

=IF(WEEKDAY(I$5,2)>=6,"",IF(

 

I'm not very adept at this stuff. 

I added the formula to both these lines. 

It skips sat and sun, but does not add those 2 days to the following week.

In other wards it it reduces the "8" days to "6"

k_squared_1-1597433898254.png

 

@Sergei Baklan 

@k_squared 

Sorry, I totally don't remember what this worksheet do.

In addition please change is 

,NETWORKDAYS.INTL($F9,I$5,11)

11 on 1

,NETWORKDAYS.INTL($F9,I$5,1)

This parameter here is

image.png

I hate to ask, could you post the complete formula? Not sure where to insert what you sent.

@Sergei Baklan 

Hi Sergei, 

not sure if you saw my last question. I am unskilled at formulas. Can you post the entire formula so that I can just cut and paste. I cannot make the last one work and probably just entering it incorrectly.

My goal is: Enter 10 days and 10 days will show on the chart but skipping over Sat and Sunday.

Thanks so much for your assistance. Greatly appreciated. @k_squared 

@k_squared 

Please check in attached file

Oh man...works perfectly. 

Thank you so  much! @Sergei Baklan 

@k_squared , you are welcome, glad to help