# Schedule - Date - Formula - 200 articles - +/- 1 month

1. ## Schedule - Date - Formula - 200 articles - +/- 1 month

Hi gurus,

Been given a challenge to deliver 200 articles with a tolerance of +/- 1 month with following requirements:

- 200 Articles have same warranty dates
- Delivery Tolerance +- 1 month (Early September - Early November)
- Delivery Time must be only 10 am or 2 pm
- Delivery only 2 articles per day (10 articles per week)

- Date Format: 20/03/2018 10:00:00

Monday:
- 1 article @ 10 am
- 1 article @ 2pm
Tuesday:
- 1 article @ 10 am
- 1 article @ 2pm
Wednesday
- 1 article @ 10 am
- 1 article @ 2pm
Thursday
- 1 article @ 10 am
- 1 article @ 2pm
Friday
- 1 article @ 10 am
- 1 article @ 2pm

I have created a XL sheet with 2 manual scenarios with a delivery of 10 articles per week and 20 articles per week

Just wondering if someone could assist me on how to either get a formula or a VBA code.

Thank you.

2. ## Re: Schedule - Date - Formula - 200 articles - +/- 1 month

I'm not sure why Thursday 1st November is not included - is that a holiday for you? If so, do you have a list of holiday dates to be avoided?

Pete

3. ## Re: Schedule - Date - Formula - 200 articles - +/- 1 month

Hi Pete,

I'm not sure why Thursday 1st November is not included - is that a holiday for you?
-> Company is based in Europe.

January, 1
March, 30
April, 2
May, 1
May, 10
May, 21
June, 23
August, 15
November, 1
December, 25
December, 26

4. ## Re: Schedule - Date - Formula - 200 articles - +/- 1 month

I'm not really sure why the block of columns based on 10 articles per week starts on 29th October, whereas the block based on 20 items per week starts on 22nd October. Can you explain the difference?

Pete

5. ## Re: Schedule - Date - Formula - 200 articles - +/- 1 month

oops my bad, it was a test.

Basic formula in G3 and L3
=B3-35

-- Where 35 is the approximate tolerance of (-)1 month --

6. ## Re: Schedule - Date - Formula - 200 articles - +/- 1 month

If I understand correctly there should be as many delivery days before as after the warranty date. If that is correct then the proposed method of filling dates may be helpful.
1) A table is set up in R14:S20 to calculate start and end dates as well as to keep track of deliveries per date.
2) The formulas for the start and end (for check purposes) dates respectively:
Formula:
`Please Login or Register  to view this content.`
Formula:
`Please Login or Register  to view this content.`

3) The formulas that fill the dates in the Formula Schedule columns are similar to:
Formula:
`Please Login or Register  to view this content.`

4) The lines the mark week changes are produced using the following formula as a conditional formatting rule: =\$J3<>\$J4
Let us know if you have any questions or if I have misunderstood what you need.

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1