+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    01-11-2013
    Location
    London
    MS-Off Ver
    Excel 2013, 2016
    Posts
    95

    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.
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    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. #3
    Registered User
    Join Date
    01-11-2013
    Location
    London
    MS-Off Ver
    Excel 2013, 2016
    Posts
    95

    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.

    Please exclude following dates:

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

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    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. #5
    Registered User
    Join Date
    01-11-2013
    Location
    London
    MS-Off Ver
    Excel 2013, 2016
    Posts
    95

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

    oops my bad, it was a test.

    XL uploaded again...

    Basic formula in G3 and L3
    =B3-35

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

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    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: copy to clipboard
    Please Login or Register  to view this content.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    3) The formulas that fill the dates in the Formula Schedule columns are similar to:
    Formula: copy to clipboard
    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula to get a schedule date and time based on a warranty date
    By dude6571 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 03-12-2018, 05:27 AM
  2. Formula for updating publish dates for written articles
    By ferrellbh in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-22-2014, 11:20 AM
  3. [SOLVED] Formula to return End OF Month date with non-calendar month lengths.
    By Snoddas in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 05-09-2014, 08:45 AM
  4. Replies: 4
    Last Post: 06-20-2013, 02:31 PM
  5. Replies: 5
    Last Post: 10-04-2012, 07:06 AM
  6. Formula to strip leading articles
    By Tiktock in forum Excel General
    Replies: 4
    Last Post: 10-19-2010, 08:10 PM
  7. Formula to change work schedule from month to month
    By ibtrue in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-29-2010, 04:55 AM

Bookmarks

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