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!
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,111,910
Messages
5,541,542
Members
410,549
Latest member
Anaarchie
Top