# Change Weekend using NETWORKDAYS

#### anthracitis

##### New Member
hlo

ive made a calculator to calculate TAT's by entering Start Date and End Date. Excel has a function "networkdays" which is what i've used. the problem is that we run on a Fri/ Sat weekend, and this formula takes a Sat/ Sun weekend, for which i've offset the start date and end date by one day each. it works fine till i don't define a holiday list. Once a do that, some bug creeps in. Take a look at the attached files..oops i dont think i know how to attache files yet, so here's the formula tht im using

=NETWORKDAYS(A2+1,B2+1,Holiday!A\$2:A\$5)-1

any help appreciated!

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
thanks!

thanks peter!

so now, does anybody know how change weekends on excel?

Adding 1 to the dates is the correct approach but you also need to add 1 to the holiday range - that change also means that you need to confirm the formula with CTRL+SHIFT+ENTER

=NETWORKDAYS(A2+1,B2+1,Holiday!A\$2:A\$5+1)

thanks

thanks barry, in fact, thats wht i've done since my last post, i've offset the holiday dates by one on a seperate sheet for the modfied dates. i didnt need to use ctrl+shif+enter though, what exactly do u say tht does?

thanks!

If you're just creating a separate column of dates, 1 greater than your holidays then, as you have discovered, you can just reference that column, e.g.

=NETWORKDAYS(A2+1,B2+1,Holiday!B\$2:B\$5)

which only requires ENTER.

My suggestion means that you don't need an additional column of dates but because you are adding 1 to an array of dates, for my formula above you'd need CTRL+SHIFT+ENTER

got tht, i might not use it though.

i've screwed up my excel though, it says

Quote

Error No. = 91
Error Discrp: = Object variable or with block variable not set

You may have to manually set a reference to the [VBIDE = VBE6EXT.OLB]

Unquote

it probably happened cos i didnt read the instructions while installing the htmlmaker

You can fix that, it's in the "known issues" within the HTMLmaker instructions

Tools > Macro > Security > Trusted Publishers

hehe..as i said, i didnt read the instructions properly

Replies
2
Views
135
Replies
12
Views
515
Replies
3
Views
90
Replies
8
Views
1K
Replies
4
Views
385

1,219,812
Messages
6,150,367
Members
450,955
Latest member
rose8693

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back