Order Delivery Schedule

[nescafe]

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.

[Domenic]

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!

[nescafe]

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.

[Domenic]

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

=WORKDAY(A1,4,IV2:IV12)

Hope this helps!

[nescafe]

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.

[Domenic]

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!

[nescafe]

it does work man. Now I am more or less ready with the sheet, untill and unless some one comesup with some more upgradation. thanks a tonne.

I would love to understand the working of the formula.
=(WEEKDAY(A1,2)<6)*(1-ISNUMBER(MATCH(A1,C1:C10,0)))
I understand not a bit of it. just a bit of explanation of the logic behind this formula will help in the future.

Thanks a lot!

[Domenic]

Let's assume that the date entered in A1 is 2/28/2005, and the date is not included in C1:C10 which holds your list of holidays...

(WEEKDAY(A1,2)<6) equals TRUE

(1-ISNUMBER(MATCH(A1,C1:C10,0))) equals 1

Therefore,

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

Since the formula returns 1, the condition is considered TRUE and the entry is allowed. If the formula returned 0, the condition would be considered FALSE and the entry would be disallowed or an error message would appear, depending on the setting for Data Validation.

Note that conditional statements like...

(WEEKDAY(A1,2)<6)

...return TRUE or FALSE. The numerical equivalent of TRUE and FALSE is 1 and 0. Therefore...

=TRUE*TRUE equals 1
=TRUE*FALSE equals 0
=TRUE+TRUE equals 2
=TRUE*3 equals 3

...and so on.

This part of the formula...

(1-ISNUMBER(MATCH(A1,C1:C10,0)))

...breaks down as follows:

MATCH(A1,C1:C10,0) returns the position of A1 within C1:C10. If C1:C10 doesn't contain A1, the formula returns #N/A.

ISNUMBER(MATCH(A1,C1:C10,0)) returns TRUE if MATCH() returns a number, and FALSE if it returns #N/A.

In our example...

(1-ISNUMBER(MATCH(A1,C1:C10,0))) equals (1-FALSE), which equals 1

Hope this helps!