WORKDAY function

[ericwkn]

As everyone realizes that WORKDAY function can return a working date that exclude weekends and any dates identified as holidays. However, what if I want to return a working date excluding my designated holidays but including weekends?

For example,

Holidays are 1 Jun 2009, 2 Jun 2009 and 4 Jun 2009

Start Date: ???
Finish Date: 8 Jun 2009
Duration: 5 days

The Start Date should be 30 May 2009.

Seems like I am not able to use WORKDAY function to calculate the start date...any idea? Thanks for great help in advance

[shg]

Please take a few minutes to read the forum rules.

We don't do URGENT. If your need is urgent, please post in the commercial services forum.

[ericwkn]

Alright. Got it.

[shg]

This formula is from Bob Phillips:

=daStart + IF(nDays=0, 0, SIGN(nDays) * SMALL(IF(ISNA(MATCH(daStart + SIGN(nDays) * (ROW(INDIRECT("1:" & ABS(nDays) * 10) ) ), Holidays, 0) ), ROW(INDIRECT("1:" & ABS(nDays) * 10) ) ), ABS(nDays ) ) )

It's an array formula, which means it MUST be confirmed with Ctrl+Shift+Enter, not just Enter.

[ericwkn]

Hi Shg, thanks for your reply. It works fine. Do you mind simply explaining this formula?

[ericwkn]

Does anyone know why below error is causing error?

Using the formula in BLUE is working fine. I would like to blank out the cell if A4 or B4 is blank. Is there any alternative way to get around?


=IF(OR($A4="",$B4=""),"",AO4+IF(H4=0,0,SIGN(H4)*SMALL(IF(ISNA(MATCH(AP4+SIGN(H4)*(ROW(INDIRECT("1:"&ABS(H4)*10))),INDIRECT(E4),0)),ROW(INDIRECT("1:"&ABS(H4)*10))),ABS(H4))))

[Paul]

Did you remember to press CTRL+SHIFT+ENTER after adding the new formula section? It's still an array formula, so you need to do that.

[ericwkn]

Hi Paul, thanks for the quick response. I did.

Running "=AO4+IF(H4=0,0,SIGN(H4)*SMALL(IF(ISNA(MATCH(AP4+SIGN(H4)*(ROW(INDIRECT("1:"&ABS(H4)*10))),INDIRECT(E4 ),0)),ROW(INDIRECT("1:"&ABS(H4)*10))),ABS(H4)))) " with CTRL+SHIFT+ENTER is fine.

But I would like to blank out this cell if cell A4 or B4 is blank, what should I do?

[Paul]

Your updated formula should work just fine.

Please Login or Register  to view this content.

After typing it, just press CTRL+SHIFT+ENTER. Adding the check for blanks shouldn't generate an error if the rest of the formula has remained the same. I don't have any data, but on my sheet it returns blank until I put something into A4 and B4, at which point it returns 0.

[ericwkn]

I have attached my excel file for your reference

Take a look at the AN4 cell, it doesn' work with "=IF(OR($A4="",$B4=""),"",AO4+IF(H4=0,0,SIGN(H4)*SMALL(IF(ISNA(MATCH(AP4+SIGN(H4)*(ROW(INDIRECT("1:"&ABS(H4)*10))),INDIRECT(E4),0)),ROW(INDIRECT("1:"&ABS(H4)*10))),ABS(H4))))".

By the way I am using excel 2003. Thanks for help in advance.

[squiggler47]

probably a silly question but wouldn't it just be easier to subtract the number of days then check how many holidays fall between the 2 dates calculated and subtract them, since weekends arent a problem?

[ericwkn]

This way doesn't work. The calculated start date may fall into the holiday list.

[kaaver]

Hello,

I have a similar problem and reading above replies, I think someone of you can help me. I would very much appreciate it if you would take a look at question below.

The workday function can return the end date based on start date and duration, excluding weekends. However, I want to calculate start date based on end date and duration also weekends.

For example=

Start Date: ?
Finish Date: 27 Jul 2010
Duration: 3 days

The Start Date should be 24 July 2010

Many thanks in advance for your help!

Regards,

Kaatje

[DonkeyOte]

Please ask questions in your own thread - not the thread of another member.

edit: as you have done since posting above

[Bob Phillips]

probably a silly question but wouldn't it just be easier to subtract the number of days then check how many holidays fall between the 2 dates calculated and subtract them, since weekends arent a problem?

But what if one of thos holidays is between the originally calculated end date and then the adjusted end date. You would then need to calculate how many of those days are in the holday list, and add them back in. Then you have to do it all again , and again, and again, and ...