challenging formula

[Sony2011]

I need a formula to show "on-time","late","advance" according to the conditions and rules below.

it is quite complicated, I need a masterhand to help

Condition : on-time delivery rule is "Order date" + 1 working day or holiday or weekend, but cannot accept order date + >1 working day , which is late, and if delivery date is earlier than order date, it is advance

Condition: saturday, sunday is not working day

For example@
assume the order date = 9 June(thu),
if delivery is on 8 June or before, it is "advance"
if delivery is on 9 June, it is "on-time"
if delivery is on 10 June, it is "on-time"
if delivery is on 11 June or afterwards, it is "late" (because order date+2 working days, even though 11 June is saturday i.e. not working day)

with additional assumption:
assume 10 June (Fri) is holiday

If delivery is before 14 June (i.e. delivery on 9,10,11,12 June), it is still "on-time" (because it meets order date+1 working day or holiday or weekend)

If delivery is on 14 June or afterwards, it is "late"

assuming i allocate the cells to record the holidays at E1:E10.

what formula can meet the above conditions and rules?

headache....




then the delivery on 11 and 12 June is still ok, treat it as on-time.

 

[barry houdini]

Assuming order date is A2 and delivery date is B2 try

=IF(A2< B2,"advance",IF(B2 >WORKDAY(A2,1,E$1:E$10),"late","on-time"))

 

[Andrew Poulsom]

Have a look at the NETWORKDAYS and WORKDAY functions in Excel Help. They are in the Analysis ToolPak Add-In prior to Excel 2007 and the Add-In must be installed.

 

[Jonmo1]

Try
=LOOKUP(NETWORKDAYS(A2,B2,$E$1:$E$10)-1,{-9.99999999999999E+307,0,2},{"Advance","On-Time","Late"})

A2 = Order Date
B2 = Delivery Date
E1:E10 = Holidays

This requires the Analysis toolpack from Tools - Addins
If in XL2007 or later, this is already installed by default.


Hope that helps.

 

[barry houdini]

Hello jonmo,

I think you need to use WORKDAY rather than NETWORKDAYS as per my suggestion. Why? because in this example...

For example@
assume the order date = 9 June(thu),
if delivery is on 8 June or before, it is "advance"
if delivery is on 9 June, it is "on-time"
if delivery is on 10 June, it is "on-time"
if delivery is on 11 June or afterwards, it is "late" (because order date+2 working days, even though 11 June is saturday i.e. not working day)

11 June is "late" but 10th June is "on-time", both return 2 when you use

=NETWORKDAYS(A2,B2,holidays)

 

[Jonmo1]

Hello jonmo,

I think you need to use WORKDAY rather than NETWORKDAYS as per my suggestion. Why? because in this example...



11 June is "late" but 10th June is "on-time", both return 2 when you use

=NETWORKDAYS(A2,B2,holidays)

Quite right, thanks...

I think yours should be

=IF(A2> B2,"advance",IF(B2 >WORKDAY(A2,1,E$1:E$10),"late","on-time"))

A2 is order date, B2 is Delivery date..

 

[barry houdini]

Indeed! Thanks jonmo, that's better

 

[Sony2011]

thank you all

But, why when I applied the formula, it gives me the result #VALUE!?
anything wrong with WORKDAY?

my excel is 2007 already, i can use the workday, but seems WORKDAY give me the serial number of the date instead of the date, can it be compare with (B2>WORKDAY (A2,1, etc).

here is the formula I applied.

=IF(A2> B2,"advance",IF(B2 >WORKDAY(A2,1,E$1:E$10),"late","on-time"))
A2 is order date, B2 is Delivery date.. <!-- / message --><!-- sig -->

 

[barry houdini]

If you get #VALUE then that probably means that A2 is not recognised as a date - how do you get the value in A2, what does it look like?