+ Reply to Thread
Results 1 to 3 of 3

Calculate next Scheduled Service date based off a regular schedule

  1. #1
    Registered User
    Join Date
    02-01-2019
    Location
    Alberta
    MS-Off Ver
    2016
    Posts
    3

    Calculate next Scheduled Service date based off a regular schedule

    excel.png

    I work for a dispatching company of sorts. We have to record a clients service schedule and regular pick up days in a week or month.
    I am trying to figure out a formula that will allow a cell to show the next scheduled service date based of certain conditions in cells.

    ie. if they are 4 x wk, and the next cell over is M/Tu/W/Th/Su, I want the next cell over to show the next date service is scheduled to occur (after today's date)

    any help would be appreciated! bare with me, first time posting on this site.

    Thank you for any help!

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,123

    Re: Calculate next Scheduled Service date based off a regular schedule

    Welcome to the forum and congratulations on providing a good subject.

    A word of advice, you will probably get more responses if you attach a sample workbook with enough data to demonstrate what you are trying to do. Also sometimes it helps if you can mock up what your want the end result to look like and explain the steps you took to get from the raw data to one or two of the desired results.

    Also, looking at the picture, I'd advise a more "flat" layout for the data.

    Take a look at the sample workbook I've provided.

    It consists of a table of customers and the days of the week that they get deliveries. I have a calculation table in Columns F:H. Column G contains the date for the day of the week in the current week. Column H looks at these dates, and if the date has already passed this week, it adds 7 days meaning that the delivery will be next week. If the day of the week had not yet passed, then a delivery will be made on that day this week.

    I use a VLOOKUP in the table in columns A:C to get the next delivery dates.

    Then I have two pivot tables:

    The one in column J shows the date and which customers get deliveries on this date.

    The one in column L shoes the customers and the dates on which they get deliveries.

    The "flatter" you can make your data, the more you can do with it.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    02-01-2019
    Location
    Alberta
    MS-Off Ver
    2016
    Posts
    3

    Re: Calculate next Scheduled Service date based off a regular schedule

    Thank you very much! it does make sense to make it more "flat" i was working backwards and putting to much info in the beginning when i can have it change at the end.

    this helped a ton!!!

+ 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] Calculate meal periods based on total scheduled hours
    By Motoxboi26 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-13-2018, 02:52 AM
  2. [SOLVED] Calculate 'REAL' end date based on public holidays/scheduled 'breaks'
    By Smurg in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-19-2015, 01:26 AM
  3. Replies: 16
    Last Post: 12-06-2012, 12:18 PM
  4. Replies: 1
    Last Post: 10-31-2012, 10:11 PM
  5. Replies: 4
    Last Post: 09-26-2012, 11:21 PM
  6. [SOLVED] Formula to calculate total service date?
    By LDouble3 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-11-2012, 06:20 PM
  7. trying to calculate regular days in date range excluding holidays
    By DKY in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-14-2008, 09:12 PM

Tags for this Thread

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