I have a range of data that a user will constantly update. There are
four columns:

ArrivalTime (Time)
Appointment or Walk-In (A or W)
Appointment Time (Time)
Wait Time (Calculation of Time)

The user will enter:
Arrival Time in (B2)
A or W in (C2)
Appointment Time (if C2 = "A") in (D2).
and so on in B3, C3, D3

What I want to do is in Column E, calculate the Wait time accordingly
when a user clicks a button.
If Appointment - WaitTime = Now - Appointment Time
If Walk-In - WaitTime = Now - Arrival Time

Now the user may enter multiple rows at one time and then click the
update button.

How do I have it run through the range and run the calculations?

Brendon

Aircode (for cell E2):

=If(C2="A", IF(NOW()D2,NOW()-D2),""),NOW()-B2)

However, NOW() will continually update each time the sheet updates, and
while that gives you a picture of how long everyone has been waiting, it
doesn't stop the clock when a person actually starts their appointment. The
same would be true if you do this all in VBA; if you want to store their
final wait time, you'll need to add one more column with a flag to indicate
that an appointment has started, or, have your VBA be row-specific (only
update the active row).

HTH,
Keith

"Fester" wrote:

I have a range of data that a user will constantly update. There are
four columns:

ArrivalTime (Time)
Appointment or Walk-In (A or W)
Appointment Time (Time)
Wait Time (Calculation of Time)

The user will enter:
Arrival Time in (B2)
A or W in (C2)
Appointment Time (if C2 = "A") in (D2).
and so on in B3, C3, D3

What I want to do is in Column E, calculate the Wait time accordingly
when a user clicks a button.
If Appointment - WaitTime = Now - Appointment Time
If Walk-In - WaitTime = Now - Arrival Time

Now the user may enter multiple rows at one time and then click the
update button.

How do I have it run through the range and run the calculations?

Brendon

This works, but if the appt time is greater, then it doesn't calculate
(or show " ")

I guess my end result would be to loop through each cell, look for the
A or W, and calculate the wait time and add to the cell (E2), is this
possible, or does it need to be done one by one?


On Sep 16, 3:51*pm, ker_01 wrote:
Aircode (for cell E2):

=If(C2="A", IF(NOW()D2,NOW()-D2),""),NOW()-B2)

However, NOW() will continually update each time the sheet updates, and
while that gives you a picture of how long everyone has been waiting, it
doesn't stop the clock when a person actually starts their appointment. The
same would be true if you do this all in VBA; if you want to store their
final wait time, you'll need to add one more column with a flag to indicate
that an appointment has started, or, have your VBA be row-specific (only
update the active row).

HTH,
Keith



"Fester" wrote:
I have a range of data that a user will constantly update. *There are
four columns:

ArrivalTime (Time)
Appointment or Walk-In (A or W)
Appointment Time (Time)
Wait Time (Calculation of Time)

The user will enter:
* Arrival Time in (B2)
* A or W in (C2)
* Appointment Time (if C2 = "A") in (D2).
and so on in B3, C3, D3

What I want to do is in Column E, calculate the Wait time accordingly
when a user clicks a button.
* If Appointment - WaitTime = Now - Appointment Time
* If Walk-In - WaitTime = Now - Arrival Time

Now the user may enter multiple rows at one time and then click the
update button.

How do I have it run through the range and run the calculations?

Brendon- Hide quoted text -

- Show quoted text -