LinkBack URL
About LinkBacks
Is there a way of making NETWORKDAYS not count weekends and count Bank Holidays? Thanks
Network days
Is there a way of making NETWORKDAYS not count weekends and count Bank Holidays? Thanks
Last edited by fithawk; 11-05-2011 at 12:07 PM.
By default it only excludes weekends. You have to feed it a list of other days to exclude if you require.
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Can you give some examples - I'm not clear what you want to do?
Audere est facere
Hi. I want to to automate the hours taken column by inserting a date (attached). ThanksOriginally Posted by daddylonglegs
You're not having much luck with attachments today.
Dom
Yer Tiz........I ope!
Fithawk,
please don't quote entire posts unnecessarily - Thx
Please explain. I'm new to this forum. Thanks
Worth a read.
Dom
I see the attachment but I still don't know what you want to do
Are you trying to amend the formula in column B or something else?
DOMI see the attachment but I still don't know what you want to do
Are you trying to amend the formula in column B or something else?
Many thanks for the help. I've cracked it now so will mark as solved.
I have been watching this thread and now that you have solved it I would be very interested to see the answer. (The forum encourages that, so someone else doing a search to solve the same problem will find the answer.)
I think this can be solved with a formula similar to this
=B3-A3+NETWORKDAYS(A3,B3)-NETWORKDAYS(A3,B3,Sheet2!$A$2:$A$6)
where Sheet2!$A$2:$A$6 has a list of bank holidays. This basically uses NETWORKDAYS to figure out the number of bank holidays in a time period. However, the bank holidays must be specified to fall on a weekday, though I guess that's a given.
Jeff
| | |·| |·| |·| |·| | |:| | |·| |·|
Read the rules
Use code tags to [code]enclose your code![/code]
Isn't that the wrong way round 6String? that version could end up with a result higher than the number of days between A3 and B3.....assuming the requirement is to count all days between A3 and B3 inclusive except holidays then I think you'd need to amend that like this
=B3-A3+1+NETWORKDAYS(A3,B3,Sheet2!$A$2:$A$6)-NETWORKDAYS(A3,B3)
If holidays might be at weekends then this version would work in any version of excel
=SUMPRODUCT((COUNTIF(Sheet2!$A$2:$A$6,ROW(INDIRECT(A3&":"&B3)))=0)+0)
....or in Excel 2010 only you can use NETWORKDAYS.INTL function
=NETWORKDAYS.INTL(A3,B3,"0000000",Sheet2!$A$2:$A$6)
Yes ddl, I did seem to have it backwards. I am not quite following your SUMPRODUCT solution, but I'll have a look. I still curious as to what fithawk came up with.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks