Search code examples
google-sheetsformulaarray-formulas

google sheet Networkday.intl

I am currently looking for the number of working days deducting the holidays and the weekends. I'm using this formula right now. 

 =NETWORKDAYS.INTL(E2,F2,1,Holidays!A1:A19)

putting the list of holidays on another sheet. It worked on the first cell,I enter the formula. However the numbers on the holidays range keeps on increasing, When I copy and paste into the next cell. 

 =NETWORKDAYS.INTL(E3,F3,1,Holidays!A2:A20)

This is for the 2nd cell and for the 3rd cell,

 =NETWORKDAYS.INTL(E4,F4,1,Holidays!A3:A21)

I would like to ask if there is anything that I can do, When pasting the formula on each cell without moving the Holiday range. I tried entering the formula one by one and it worked. But, I am looking for an easier way for future use. Thanks a lot in advance.

Solution

  • To lock a range use $ 

      =NETWORKDAYS.INTL(E2,F2,1,Holidays!A$1:A$19)

    This will lock the rows. So if you drag fill down or copy paste down 1 and 19 won't change. But if you drag fill to the right,A will become B and so on. To make a full lock, use $ on both columns and rows.

     =NETWORKDAYS.INTL(E2,F2,1,Holidays!$A$1:$A$19)