Change Weekend using NETWORKDAYS

[anthracitis]

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!

 

[Peter_SSs]

anthracitis

Welcome to the Mr Excel board!

..oops i dont think i know how to attache files yet

Have a look at this:
http://www.mrexcel.com/board2/viewtopic.php?t=92622

 

[anthracitis]

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]

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]

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]

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]

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]

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

 

[anthracitis]

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