• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

More than one holiday ranges in Workday Function

Hi KartikiJ, and welcome to the forum! :awesome:

I'm not sure which post you got that formula from, so I have a hard time evaluating your post. The thread you linked to has several good suggestions.
 
Hi ,

The problem seems to be that the second range is on a different sheet ; the SMALL function is generating an error. Ensuring that both the ranges are on the same sheet gives the correct value.

Narayan
 
Hi ,

I think the only way is to use formulae to get all the ranges in one tab , and then use these new ranges in your WORKDAY formula.

Narayan
 
On a side note, I would ask why are you having the holidays scattered? Databases work much better when your data is consolidated, not when it's scattered across ranges/sheets.
 
I moved these ranges to one worksheet.. This is the current code in VBA

Code: 
Worksheets("Schedule").Cells(Target.Row, 7).Value = WorksheetFunction.WorkDay(strdt, noofdays, WorksheetFunction.Small((A4:A103,Sheets("Resource Leave Plan").Range(holday & "4:" & holday & "103")),ROW(INDIRECT("1:"&COUNT(A4:A103,Sheets("Resource Leave Plan").Range(holday & "4:" & holday & "103"))))))

it gives error in debugging.. ') expected' and INDIRECT function cannt be written in VB too.. how do I get it to work?
Resource leave Plan is the same worksheet where I have A4:A103 range
 
Last edited:
Hi ,

You started off by posting a formula , and now suddenly you have switched to VBA.

Can you first post the worksheet formula that you wish to implement using VBA ?

Narayan
 
Code: 
=WORKDAY(K8,K9,SMALL((Sheet1!D5:D12,Sheet1!F5:F11),ROW(INDIRECT("1:"&COUNT(Sheet1!D5:D11,Sheet1!F5:F11)))))

this formula is written on Sheet2
 
Does this formula even work?

If I use =WORKDAY(E3,E4,B2:C4), it considers any holidays entered in 'B2:B4'

But if I enter,
=WORKDAY(E3,E4,SMALL((B2:B4,C2:C3),ROW(INDIRECT("1:"&COUNT(B2:B4,C2:C3)))))
it does not even consider holidays in B2:B4.. Is there any other solution for multiple holiday ranges?
 
Hi ,

The following formula , which you posted earlier , works correctly , provided it is entered as an array formula , using CTRL SHIFT ENTER :

=WORKDAY(K8,K9,SMALL((Sheet1!D5:D12,Sheet1!F5:F11),ROW(INDIRECT("1:"&COUNT(Sheet1!D5:D12,Sheet1!F5:F11)))))

Narayan
 
You must log in or register to reply here.
Back
Top