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.