# Workday Formula

#### Cpinhey

##### New Member
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?

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### Jonmo1

##### MrExcel MVP
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

##### New Member
I think that has done the trick.

#### Jonmo1

##### MrExcel MVP

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)

Last edited:

#### Cpinhey

##### New Member

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

#### barry houdini

##### MrExcel MVP
...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

##### New Member
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.

Replies
4
Views
84
Replies
18
Views
295
Replies
5
Views
164
Replies
5
Views
88
Replies
0
Views
76