Jul 09 2019 01:00 PM
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,""))
Jul 09 2019 01:25 PM
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,"")))
Jul 09 2019 01:56 PM
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: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,"")))
Jul 09 2019 02:11 PM
Did you check the file I applied to my previous post? Updated formula is applied to couple of first rows.
Jul 09 2019 02:19 PM
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:Did you check the file I applied to my previous post? Updated formula is applied to couple of first rows.
Jul 09 2019 02:28 PM
Stay on the top left cell (I9) and drag it to the right till end of the time scale
Next drag entire this row down till end of your range.
Aug 14 2020 06:10 AM
Could you provide the same spreadsheet but skipping sat and sunday?
Thankyou @Sergei Baklan
Aug 14 2020 08:38 AM
At the beginning of the formula above please change
=IF(WEEKDAY(I$5,2)=7,"",IF(
on
=IF(WEEKDAY(I$5,2)>=6,"",IF(
Aug 14 2020 12:39 PM
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"
Aug 14 2020 01:56 PM
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
Aug 16 2020 05:30 PM
I hate to ask, could you post the complete formula? Not sure where to insert what you sent.
Aug 19 2020 06:27 AM
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
Aug 19 2020 12:46 PM
Please check in attached file
Aug 19 2020 01:06 PM
Oh man...works perfectly.
Thank you so much! @Sergei Baklan
Aug 19 2020 01:37 PM
@k_squared , you are welcome, glad to help