# Calculate next Scheduled Service date based off a regular schedule

1. ## 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. ## 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.

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!!!

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