IF function using WORKDAYS and a Bank Holiday list - trying to add new holiday dates

**jowarks** · 07-04-2013, 05:44 AM

Hi,
Hoping someone can help.
I have 'inherited' a spreadsheet from a colleague that has left the company and I am trying to update a function.
Basically, we have columns for
Start Date - Which is set for staff to enter a date
Automatic calculation of due date - (IF formula to calculate 10 working days from the 'Start Date', so excluding weekends and bank holidays.)

Now the bank holiday list that we have embedded in a separate sheet only went up to the start of 2013. So I am attempting to change the formula to look at a longer list of bank holidays.
I am pulling out my hair trying to get the formula to accept the new data set. I have tried manually changing the new cell it goes down to in the fx bar (i.e. A3 - A38) but it keeps reverting back.

I can't remember how to 'set' the Bank Holiday list with regard to working days function.
Any suggestions?

TIA

**TMS** · 07-04-2013, 06:16 AM

Hello and welcome to the forum.

You may get more input if you share the formula and, ideally, a sample workbook.

Regards, TMS

**DBY** · 07-04-2013, 06:17 AM

Hello
You just need to reference the range that the holiday list is in, for example:

Please Login or Register  to view this content.

But better still I think is to create a dynamic named range to hold your holiday list which you can then add to or subtract. Below is a link to a video that shows a method using the Excel Table feature:

http://www.youtube.com/watch?v=2O3fvlBHLzE

Hope this helps.
DBY

**jowarks** · 07-04-2013, 06:34 AM

Thanks for the suggestions. I can change individual cells to cover the range but it won't change the whole column for me (500 rows) it seems to want me to change them all individually which can't be right!

That video is great, and I will definitely do that in the future with spreadsheets I create. I am struggling to work it into this one that I didn't 'own' because some of the formulas she has used are ones which I am not familiar with, so I am just trying to do alterations rather than set up the formula again (because I'm not sure I'd know how!)

I'm going to try and extrapolate into a separate spreadsheet so I can provide sample workbook without it having any confidential information in it.

**jowarks** · 07-04-2013, 06:36 AM

PS, TMS, here is the formula
I need it to read $A3:$A$45 and whilst I can edit this in individual cells, it's not letting me do it in bulk to the whole column

=IF(L7="","",WORKDAY(L7,10,Sheet3!$A$3:$A$13))

**DBY** · 07-04-2013, 06:56 AM

Hello
TMS appears to be offline at the moment but if I can make a suggestion, you don't need to select each individual formula. Select the whole range of formulas, then click in the Formula Bar to edit the formula, then enter the changes with CTRL + ENTER. This should edit the whole range.

DBY

**TMS** · 07-04-2013, 07:23 AM

DBY's approach should address your problem for propagating the formula.

However, the formula entered must relate to the first row selected.

So, if, for example, that formula relates to row 7, we're OK, and you can select the cell in row 7 down to the cell in the last row of data, and, in the formula bar type:

=IF(L7="","",WORKDAY(L7,10,Sheet3!$A$3:$A$45))

and press Ctrl-Enter.

The other option is to key the formula into row 7 and then, either copy it and paste it into the rest of the column, or use the autofill handle in the bottom right hand corner of the cell.

Regards, TMS

**jowarks** · 07-05-2013, 05:04 AM

THANK YOU both so much. A combo of using Ctrl Enter and then using the autofill handle in the bottom right corner of the cell seems to have done the trick.
You have both saved me a lot of time wasting and cursing my ex colleague!

**TMS** · 07-05-2013, 05:42 AM

You're welcome. Thanks for the rep.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

IF function using WORKDAYS and a Bank Holiday list - trying to add new holiday dates

LinkBack

Thread Tools

Rate This Thread

Display

IF function using WORKDAYS and a Bank Holiday list - trying to add new holiday dates

Re: IF function using WORKDAYS and a Bank Holiday list - trying to add new holiday dates

Re: IF function using WORKDAYS and a Bank Holiday list - trying to add new holiday dates

Re: IF function using WORKDAYS and a Bank Holiday list - trying to add new holiday dates

Re: IF function using WORKDAYS and a Bank Holiday list - trying to add new holiday dates

Re: IF function using WORKDAYS and a Bank Holiday list - trying to add new holiday dates

Re: IF function using WORKDAYS and a Bank Holiday list - trying to add new holiday dates

Re: IF function using WORKDAYS and a Bank Holiday list - trying to add new holiday dates

Re: IF function using WORKDAYS and a Bank Holiday list - trying to add new holiday dates

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions