• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Expected waiting time

CristianoGK

New Member
Hi Everyone,

I have a table containing the dates of arrival, start of service and departure, as shown below:
Today's date = 3/20/2021
Customer Arrival start Departure
AAA 3/19/2021 3/19/2021 3/20/2021
BBB 3/20/2021 3/20/2021 3/21/2021
CCC 3/20/2021 3/21/2021 3/22/2021
DDD 3/21/2021 3/22/2021 3/23/2021
EEA 3/25/2021 3/25/2021 3/27/2021
FFF 3/25/2021 3/27/2021 4/5/2021

It is a simple queue system, where only 1 customer can be served at a time.
However, customers who manage to anticipate the arrival pass in front of those that are still planned, staggering the dates of the next service.

I need a formula that can make a prediction of the current waiting time situation, in case someone arrives in the next few days.
In the example above, if any customer arrives on one of the days below, the actual expected waiting time would be:
3/20/2021 = 2 days
3/21/2021 = 1 day
3/22/2021 = 0
3/23/2021 = 0
3/24/2021 = 0
3/25/2021 = 11 days
3/26/2021 = 10 days
3/27/2021 = 9 days
3/28/2021 = 8 days
3/29/2021 = 7 days

If anyone knows a solution to generate such a report it would be fantastic.

It would also help a lot if there was a way to project the impact on a fictitious customer's queue. Ex: If a “XXX” customer arrives on 03/20/2021 with a forecast of 2 days of service, the queue would be adjusted to:
Customer Arrival Start Departure
AAA 3/19/2021 3/19/2021 3/20/2021
BBB 3/20/2021 3/20/2021 3/21/2021
CCC 3/20/2021 3/21/2021 3/22/2021
XXX 3/20/2021 3/22/2021 3/24/2021
DDD 3/21/2021 3/24/2021 3/26/2021
EEA 3/25/2021 3/26/2021 3/29/2021
FFF 3/25/2021 3/29/2021 4/7/2021
3/20/2021 = 4 days
3/21/2021 = 5 days
3/22/2021 = 4
3/23/2021 = 3
3/24/2021 = 2
3/25/2021 = 11 days
3/26/2021 = 10 days
3/27/2021 = 9 days
3/28/2021 = 8 days
3/29/2021 = 7 days

File attached with a model and some data sample.
73907

Question also cross-posted at: Link

Anyone know how to solve this one?
Thank you very much
 

Attachments

Top