Home

Help me skip sundays for a work week!

%3CLINGO-SUB%20id%3D%22lingo-sub-745964%22%20slang%3D%22en-US%22%3EHelp%20me%20skip%20sundays%20for%20a%20work%20week!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-745964%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20the%20formula%20that%20came%20with%20the%20template.%20I%20need%20to%20my%20days%20to%20skip%20sundays%20when%20I%20enter%20%22no.%20of%20days%22.%20I%20need%20this%20for%20a%20construction%20timeline%20but%20we%20are%20not%20working%20sundays.%20What%20can%20I%20put%20in%20the%20formula%20to%20exclude%20those%20days%3F%20Thank%20you!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(%24C9%3D%22Goal%22%2CN%245%26gt%3B%3D%24F9%2CN%245%26lt%3B%3D%24F9%2B%24G9-1)%2C2%2CIF(AND(%24C9%3D%22Milestone%22%2CN%245%26gt%3B%3D%24F9%2CN%245%26lt%3B%3D%24F9%2B%24G9-1)%2C1%2C%22%22))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-745964%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-746014%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20me%20skip%20sundays%20for%20a%20work%20week!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-746014%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373650%22%20target%3D%22_blank%22%3E%40DGideon%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20adjusted%20first%20part%20of%20the%20formula%20for%20Goal.%20For%20Milestone%20it%20shall%20be%20similar%2C%20but%20better%20to%20have%20some%20data%20to%20test.%3C%2FP%3E%0A%3CPRE%3E%3DIF(WEEKDAY(I%245%2C2)%3D7%2C%22%22%2CIF(AND(%24C9%3D%22Goal%22%2CI%245%26gt%3B%3D%24F9%2CNETWORKDAYS.INTL(%24F9%2CI%245%2C11)%26lt%3B%3D%24G9)%2C2%2CIF(AND(%24C9%3D%22Milestone%22%2CI%245%26gt%3B%3D%24F9%2CI%245%26lt%3B%3D%24F9%2B%24G9-1)%2C1%2C%22%22)))%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-746124%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20me%20skip%20sundays%20for%20a%20work%20week!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-746124%22%20slang%3D%22en-US%22%3E%3CP%3EI%20attached%20the%20file%20to%20the%20post.%20I%20tried%20pasting%20what%20you%20provided%20in%20the%20box%20and%20it%20did%20not%20fix%20my%20issue.%20It's%20still%20counting%20sunday.%20Thank%20you%20though%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bwrote%3A%3CBR%20%2F%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373650%22%20target%3D%22_blank%22%3E%40DGideon%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20adjusted%20first%20part%20of%20the%20formula%20for%20Goal.%20For%20Milestone%20it%20shall%20be%20similar%2C%20but%20better%20to%20have%20some%20data%20to%20test.%3C%2FP%3E%3CPRE%3E%3DIF(WEEKDAY(I%245%2C2)%3D7%2C%22%22%2CIF(AND(%24C9%3D%22Goal%22%2CI%245%26gt%3B%3D%24F9%2CNETWORKDAYS.INTL(%24F9%2CI%245%2C11)%26lt%3B%3D%24G9)%2C2%2CIF(AND(%24C9%3D%22Milestone%22%2CI%245%26gt%3B%3D%24F9%2CI%245%26lt%3B%3D%24F9%2B%24G9-1)%2C1%2C%22%22)))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-746154%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20me%20skip%20sundays%20for%20a%20work%20week!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-746154%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373650%22%20target%3D%22_blank%22%3E%40DGideon%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDid%20you%20check%20the%20file%20I%20applied%20to%20my%20previous%20post%3F%20Updated%20formula%20is%20applied%20to%20couple%20of%20first%20rows.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-746164%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20me%20skip%20sundays%20for%20a%20work%20week!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-746164%22%20slang%3D%22en-US%22%3E%3CP%3EWow!%20Thank%20you!%20That's%20exactly%20what%20I%20need%20it%20to%20do.%20Now%20how%20can%20I%20apply%20the%20same%20formula%20to%20the%20remaining%20sundays%20(columns)%3F%3F%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bwrote%3A%3CBR%20%2F%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373650%22%20target%3D%22_blank%22%3E%40DGideon%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDid%20you%20check%20the%20file%20I%20applied%20to%20my%20previous%20post%3F%20Updated%20formula%20is%20applied%20to%20couple%20of%20first%20rows.%3C%2FP%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-746346%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20me%20skip%20sundays%20for%20a%20work%20week!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-746346%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373650%22%20target%3D%22_blank%22%3E%40DGideon%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EStay%20on%20the%20top%20left%20cell%20(I9)%20and%20drag%20it%20to%20the%20right%20till%20end%20of%20the%20time%20scale%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122786i16216DFD2E99FDB8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ENext%20drag%20entire%20this%20row%20down%20till%20end%20of%20your%20range.%3C%2FP%3E%3C%2FLINGO-BODY%3E
DGideon
New 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,""))

5 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.

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies