Workday Formula

[Cpinhey]

Hi,

I'm setting up some workbooks for the new financial year 2012-13 and I've got a problem with a workdays formula that has worked perfectly well for this current year, 2011-12.

Here is the formula:

=IF(WORKDAY(A$1,ROW(A1)-ROW(A$1),BankHolidays)>A$2,"",TEXT(WORKDAY(A$1,ROW(A1)-ROW(A$1),BankHolidays),"dd/mm/yyyy"))

A1 = First day of year
A2 = Last day of year
BankHolidays = Named Range of UK Bank Holidays

I populate a column with the formula to get a list of all the working days for the whole year.

My problem is when I have tried this formula for this new financial year, start date 01/04/2012 and end date 31/03/2013, I keep getting 01/04/2012 as the first working day, but 01/04/2012 is a non working day (sunday) I've checked have all Bank Holidays entered correctly.

I think I'm probably missing something really simple here, would appreciate any ideas?

Or does someone have a better way of generating a list of all working days for an entire financial year?

 

[Jonmo1]

Perhaps

=IF(WORKDAY(A$1-1,ROWS(A$1:A1),BankHolidays)>A$2,"",TEXT(WORKDAY(A$1-1,ROWS(A$1:A1),BankHolidays),"dd/mm/yyyy"))

 

[Cpinhey]

I think that has done the trick.

Thanks for your help, I had to admit defeat on this one.

 

[Jonmo1]

Glad to help...

also, you can reduce the calculations considerably..

Put this in the FIRST cell (Say E1 or whatever)
=IF(WORKDAY(A$1-1,ROWS(A$1:A1),BankHolidays)>A$2,"",TEXT(WORKDAY(A$1-1,ROWS(A$1:A1),BankHolidays),"dd/mm/yyyy")+0)

Then in the rest, you only need to test the previous formula for >A2, thus reducing calculations
=IF(E1>=A$2,"",TEXT(WORKDAY(A$1-1,ROWS(A$1:A2),BankHolidays),"dd/mm/yyyy")+0)

 

[Cpinhey]

Amazing. Thank u. I'd never have thought to do it that way.

 

[barry houdini]

...but that second formula won't work correctly if the end date is a non-working day, e.g. if you have A2 = Sun 31/3/2013 as stated then you'll get dates up to 1/4/2013.

I'd suggest using just this in E1

=WORKDAY(A$1-1,1,BankHolidays)

and in E2 copied down

=IF(E1="","",IF(WORKDAY(E1,1,BankHolidays)>A$2,"",WORKDAY(E1,1,BankHolidays)))

format all cells with required date format

 

[Cpinhey]

Ah, I think i see what u mean. I've just logged off, will give this a whirl when im back in the office tomorrow morning.

Thanks you.