• 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.

Workday function query.

D

derek davis

Member
Hello everyone, could I ask why when using the bankholiday argument within the Workday function, that there appears to be a restriction on the size of the range of Bank Holiday dates the the function can read. It appears that only 17 entries will respond to the Bankholiday argument.
I have ATT: a sample file as for further understanding of this issue. If you take a look at the "Date" column and drop onto cell (Date 12) then take a look at the formula and highlight the BankHoliday argument you will notice only the first 17 possible entries are read, all the others contained within the range are ignored.

1. Is this correct.
2. Is there a workaround? so as the BankHoliday range grows the date column responds correctly and does not display a bank holiday date.

Many thanks everyone for your advise.
 

Attachments

  • work-hols test.xlsm
    379.7 KB · Views: 12
You named range BankHolidays is only looking at U2:U17, you will need to change it to look at all the dates.
 
If you use this formula in the refers to box
=OFFSET(DataInput!$U$2,,,COUNTA(DataInput!$U:$U)-1)
Then it will auto expand to cover all the dates in the column as long as you don't have any blank cells between dates
 
Hi Fluff13, I would like to maintain the existing formula structure in the "Dates" and "Bank Holiday" columns. What I don't understand (being something of a ludite with Excel) is why I am unable to expand the Bank Holiday range past U17, do you know how this would occur. If this issue is fixable then we have a solution.
 
What Fluff13 is suggesting will not change the formula structure of the Dates column. The Bank Holiday column doesn't have any structure. Nothing will need to change on the sheet.

2021-04-04_000146.png
 
Hi P45cal, thank you for explaining how Fluff13 was conveying his interpretation of my query. I now understand and have tested the answer offered. I have never used offset function before.
Thanks once again for everyone's help and time , it is highly appreciated.
 
Be aware that OFFSET is a volatile function.
As you seem to have lots of Conditional formatting ( which is also volatile I think) you might experience calculation slowdown.
If this is the case, there are non-volatile solutions.
Let us know if you get in trouble :)
 
Last edited:
I take onboard your advise regards offset. One thing I have noticed, there is a calculation slowdown before I started using offset. Do you think this due to con formatting that already exists within the worksheet?
 
As far as I can see the CF applied to A2:I1000 using ="MOD(ROW(),2)" as formula is incorrect. It should be =MOD(ROW(),2) (you omitted the = sign when entering)
Don't know if that slows things down though.
 
Hi Pecoflyer, you were correct about the error in the CF. I was evaluating the colour requirement that I wanted to use, the removal of the = was deliberate. One thing I did notice was when the = is removed the remaining functions are encapsulated in " ".
Many thanks for your assistance.
Del
 
derek davis said:
Hi Pecoflyer, you were correct about the error in the CF. I was evaluating the colour requirement that I wanted to use, the removal of the = was deliberate. One thing I did notice was when the = is removed the remaining functions are encapsulated in " ".
Many thanks for your assistance.
Del
Click to expand...
If the intent is to color alternate rows alone then use table and coloring style that suits you. This will help you get rid of conditional formatting.
 
You must log in or register to reply here.
Back
Top