Change Weekend using NETWORKDAYS

anthracitis

New Member
Joined
Sep 24, 2006
Messages
6
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

anthracitis

New Member
Joined
Sep 24, 2006
Messages
6
thanks!

C:\Documents and Settings\varun.PC305642530274\Application Data\Microsoft\AddIns\temp.htm

thanks peter!

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

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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)
 

anthracitis

New Member
Joined
Sep 24, 2006
Messages
6

ADVERTISEMENT

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!
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
 

anthracitis

New Member
Joined
Sep 24, 2006
Messages
6

ADVERTISEMENT

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 :(
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
You can fix that, it's in the "known issues" within the HTMLmaker instructions

Tools > Macro > Security > Trusted Publishers

tick the "Trust access to Visual Basic project" box
 

Forum statistics

Threads
1,141,163
Messages
5,704,667
Members
421,361
Latest member
AJPlant

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top