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.
Question also cross-posted at: Link
Anyone know how to solve this one?
Thank you very much
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.
Question also cross-posted at: Link
Anyone know how to solve this one?
Thank you very much