formula for deriving working day date

[vinod9111]

Hi All,

Preparing a calendar for reports handled by me, need a formula to derive the working day date.

I have a list of position dates for which the report needs to be published. My date of reporting will be next working day.

I also have the list of holidays with me.

For instance if my report "A" has the data till 30th April, 1st May being a labor day, a holiday , my reporting date will be 2nd May. Similarly treatment should be given for weekly holidays.

I need a formula against the position date which can give me next working day.

regards

Vinod

 

[Eric W]

Try:

=WORKDAY(A1,1,B1:B10)

Where A1 is the current day, and B1:B10 is a list of holidays to skip.

 

[vinod9111]

Thanks Eric, it worked perfectly.

IF i had to one more conditions to it, if the position date happens to be 2nd and 4th Friday, the reporting date needs to be same.

Is that possible.

regards,

Vinod

 

[Eric W]

A bit trickier, but try:

=IF(OR(A1=EOMONTH(A1,-1)+{15,29}-WEEKDAY(EOMONTH(A1,-1)+2)),A1,WORKDAY(A1,1,B1:B10))

 

[vinod9111]

Thanks eric for the formula, there is a small problem with the output. As far as 2nd and 4th Friday it shows the correct due date. But when it is 1st,3rd or 5th friday of the month, it shows the due date of Monday and not Saturday i.e. next working day.

regards
Vinod

 

[Eric W]

You didn't say that Saturday is a working day. You can switch the formula to:

=IF(OR(A1=EOMONTH(A1,-1)+{15,29}-WEEKDAY(EOMONTH(A1,-1)+2)),A1,WORKDAY.INTL(A1,1,11,B1:B10))

Which only considers Sunday to be a non-working day. But instead of 11, there are codes which consider any single day of the week, or any 2 consecutive days as non-working. Let me know what are your non-working days and I'll look it up.