Workday function to excluding fridays

**nawas** · 03-08-2011, 02:53 AM

Hi,

I would really appreciate if anyone can help me out to find out a solution,

I'm looking for a formula to get a date after excluding fridays & holidays only..

E.g: Cell A1 i've a date: 1-Feb-2011 and i should get a date in cell B1 after 100 working days (excluding Fridays & holidays)

I'm trying Workday function, but it returns after skipping Saturdays & Sundays..

Experts kindly help me out..

Regards,

Nawas

**canapone** · 03-08-2011, 03:24 AM

Hi, in D2:D100 the holidays dates

=SMALL(IF((WEEKDAY(ROW(INDIRECT(A1+1&":"&A1+365)))<>6)*(COUNTIF(HOLIDAYS,ROW(INDIRECT(A1+1&":"&A1+365)))=0),ROW(INDIRECT(A1+1&":"&A1+365))),100)

To be confirmed with control+shift+enter: it's an array formula.

The formula counts the 100th day excluding Fridays (weekday<>6) and Holidays (listed in D1:D100) from the day after in A1.

Regards

**canapone** · 03-08-2011, 04:00 AM

Hi again
I was double-checking the formula: I've substituted HOLIDAYS (you can use it, if you prevoiusly define it as a name) with D1:D100.

=SMALL(IF((WEEKDAY(ROW(INDIRECT(A1+1&":"&A1+365)))<>6)*(COUNTIF($D$1:$D$100,ROW(INDIRECT(A1+1&":"&A1+36 5)))=0),ROW(INDIRECT(A1+1&":"&A1+365))),100)

To be confirmed with control+shift+enter.

Hope this helps.

Regards

**nawas** · 03-08-2011, 04:24 AM

Originally Posted by CANAPONE

Hi, in D2:D100 the holidays dates

To be confirmed with control+shift+enter: it's an array formula.

The formula counts the 100th day excluding Fridays (weekday<>6) and Holidays (listed in D1:D100) from the day after in A1.

Regards

Hi Canapone,

So quick.... wow..its working ...

Many Thanks...

**dailyglobal** · 07-03-2011, 04:03 AM

I was also looking for that kind of solution and I have found it very much effective solution against my problem. Beside this, I was also trying minus certain no. of days using this formula but in that case its not working.

Is is possible to minus instead of adding days?

**canapone** · 07-05-2011, 11:04 AM

Hi,

an array formula, more or less same spirit.

=LARGE(IF((WEEKDAY(ROW(INDIRECT(A1-365&":"&A1-1)))<>6)*(COUNTIF($D$1:$D$100;ROW(INDIRECT(A1-365&":"&A1-1)))=0);ROW(INDIRECT(A1-365&":"&A1-1)));100)

Hope it works.

Regards

-----

Edit: better if you'd open a new thread in order to obtain more help and visibility.

**dailyglobal** · 07-06-2011, 07:10 AM

Dear Canapone,

Many thanks for quick feedback.

While I was trying to use the formula its showing an error message.

If you don't mind can you please check and show me where is the problem.

**canapone** · 07-06-2011, 07:22 AM

Hi,

maybe you forgot to confirm the formula with control+shift+enter.

Attached the test file.

Hope it helps

-----

GIORNO.SETTIMANA is the Italian equivalent for WEEKDAY

नमस्ते