Order Delivery Schedule

Hi,

I want to make an excel sheet, in which if i put the date of order I should get the Date of Delivery. Now the perimeters for calculating the Date of Delivery are:

1. The date of delivery is 4 working days from the date of order.
IMPORTANT : "4 working days."

2. The problem here is, How do I incorporate the Saturday, Sundays and the Festival Holidays of my company, into this formula.
I am ready to mark all the festval holidays in a calender. But how do I do it.

Hope I am clear with I want. Please come back doubts if any.

Waiting for responses.

Use the WORKDAY function. In addition to excluding Saturdays and Sundays, it allows you to exclude holidays...

WORKDAY(StartDate,Days,Holidays)

You'll need to make sure that the Analysis ToolPak add-in is enabled...

Tools > Add-Ins > and check Analysis ToolPak

Hope this helps!

Hi Domenic ,

Thanks a lot. That was short and to the point.
Using your advice I have been able create the formula. The formula i am using is WORKDAY(("3/16/2005"),4,IV2:IV12). It works.

I did the major part but got stuck on a small thing. My sheet looks like this: see att.

I wish to have such an excel sheet where in I can put the date in cell A1 and the cell A2 shows the result. Cell A2 would have the formula.
Now in the formula I am using, because the date has to be entered as text string (in " "). i am not able to link it to cell A1.

Looking forward to your reply.

I don't see you're attachment, but try the following...

=WORKDAY(A1,4,IV2:IV12)

Hope this helps!

My Sheet is working.
now I want to validate the cell in which I am putting the order value , so that somneone CANNOT enter dates that fall on weekend and the company annual holidays.Basically if some one enter a date that falls on a weekend or on a holiday, it should not accept.

Try the following...

1) Select your cell for Data Validation

2) Data > Validation > Settings > Allow > Custom > Formula

3) Enter the following formula:

=(WEEKDAY(A1,2)<6)*(1-ISNUMBER(MATCH(A1,C1:C10,0)))

...where A1 is your Data Validation cell, and C1:C10 contains your list of holidays.

Hope this helps!