Improve Formula Speed

[jynxy]

Hi,

I am using the following code to return a rolling 8 week shift pattern for the year, this is then multiplied by 400 colleagues,

=IF($A3<>"",IF(LEN(EMPLOYEES!$E2)>0,MID(MID(EMPLOYEES!$E2,MOD(NETWORKDAYS.INTL(EMPLOYEES!$B2,B$2,"0000000")-1,LEN(EMPLOYEES!$E2)/4)*4+1,4),1,1),""),"")

as you can image this runs slow, but i also have to do this across 3 shifts, so i have 3 of these sheets. is there a faster way to do this ?

$A3 is employee name

EMPLOYEES!$E2 is the patter so currently use the following (this is in blocks of 4, might be able reduce down to block of 3)

00000800080008000800000080008000000000008000800080000800080000000800080008000800000000008000800080008000800000000000080008000800080000008000800080008000000000000800080008000800000008000800080000000000800080008000800080000000

EMPLOYEES!$B2 is start of the rota pattern

B$2 is date to lookup so every day of the year.

I will also be adding in further sheets, Overtime, Holidays, Shift In, Shift Out, Absence, so a further 15 sheets for all 3 shift unless there is a quicker and more efficient way to do this.

Thanks for any help, and hope this makes sense.

 

[GlennUK]

A couple of questions : how slow is this formula (there is nothing that hits me as being too complex? And, are you certain that no other formulas in the worksheet/workbook are causing the slowness?