# IF Formula to calculate if product has been sent on time

1. ## IF Formula to calculate if product has been sent on time

Hi,

I pull data from a system which gives me an order date and a shipment date.

Thes data comes in this format:

Order Date Shipment Date

15/08/2012 17/08/2012

I want to return a '1' in another column for items that have been shipped within 4 days, and a '0' for items that haven't been shipped within 4 days. However, weekends don't count in the turn around time. For example, if the order was received on Wednesday 15/08/2012 and shipped on the Monday 20/08/12 this would only be 3 days, rather than 5 days.

Any help would be greatly appreciated.

Thanks

David

2. ## Re: IF Formula to calculate if product has been sent on time

hi David. assuming your data is in A2 & B2, try this formula:
=IF(NETWORKDAYS(A2,B2)-1<=4,1,0)

you can even have a list of holidays somewhere & refer to the list in the NETWORKDAYS formula. for eg. if i have a list of dates in G1:G10, then it'll be:
=IF(NETWORKDAYS(A2,B2,\$G\$1:\$G\$10)-1<=4,1,0)

3. ## Re: IF Formula to calculate if product has been sent on time

Hi

Try

=NETWORKDAYS(A1,B1)-1

A1=Order Date

4. ## Re: IF Formula to calculate if product has been sent on time

Thanks guys, worked great!

Greatly appreciated.

5. ## Re: IF Formula to calculate if product has been sent on time

Hi David,

Assuming Order Date in Col A1 & Shipment Date in Col B1 just try simple If Function :-

=IF((B1-A1)<4,1,0)

There are currently 1 users browsing this thread. (0 members and 1 guests)