Calculation based on multiple conditions

[doop4204]

Im looking to perform subtraction of time in multiple columns. I'm thinking this may be a IF logic.

What I'm trying to calculate is how long a person waits in a room by themselves until the next person comes to see them.

Starting with column N and going right. row 2-4 is where I'll be looking. Calculating the wait time is the goal. I want excel to look at various options to calculate this.

For example in row 2, Starting at O, provider left at 818 and the MA came in at 819. the wait time of 1 min would populate P. MA leaves at 8:22 and then next person doesn't come in till column AE at 8:24. the next wait time column I want it to populate is Column AB and leave the W and S wait time columns blank.

But..for example in row 4 this is a different scenario. More times are entered so excel will calculate the wait time to populate S, W and AB, but leave P blank.

Thanks for your help.

[FDibbins]

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[JeteMc]

I have put formulas in cells P2, S2, W2 and AB2 that do what you want. I did a little experimenting with cell W13 but in the end left it alone as you may already have a strategy for dealing with that instance.

Update of tracking sheet automated 061015.xlsx

Hope that this is helpful.

[doop4204]

JeteMC,

You rock. I inserted the functions and they work great. I made some adjustments to the spreadsheet. I added a column in I, L, AA, and AD. Again I'm focusing on Wait time to calculate correctly as we discussed above.

O19, O27: In this example, the provider and the MA go in at the same time or one goes in before the other so there is no wait time. In this case could the formula= 00:00:00 wait time result instead of ######?

X6: In this example the provider is the last person to see the patient and the patient leaves. Could the formulas in column X, AA and AD reflect this and have a wait time result of 00:00:00 instead of ######?

U53: Came up FALSE when the formula was inserted?

[JeteMc]

Thank You for the feedback. I have installed formulas in columns I, L, AA and AD. I checked O19 and made a general change to all of the formulas so that if the value was less than zero it would put a time of 0:00:00 in the cell. I didn't see that issue with O27 so perhaps I am misunderstanding something. As to the patient in row 6 the formula leaves the cells in columns X, AA, and AD blank, but it would be easy enough to change the formula so that the last "value_if_false" is 0, instead of "". The issue with U53 resolved when the formula was changed, and it is now blank. Here is the updated file:

Update of tracking sheet automated 061015-1.xlsx

Hope this helps.

[doop4204]

JeteMc,

Your knowledge is awesome. Sorry to be a pain but I forgot one thing. Could you total the wait time in column AO please? I tried to do a simpe calculation but because of all the conditions it was passed my knowledge. Im learning alot from your help. Thanks. again.

[doop4204]

n4-f4 will give the answer to AR4. If the provider went in early, like in this example, can the difference be shown as a negative number? If not any thoughts on how this answer can be represented by something other than #### to show that it is negative? Color perhaps?

This is a killer in column AI as well for the rooming piece.

[JeteMc]

Unless the spreadsheet has been changed, the column headers indicate that column AO is for "Notes from Sheet" and column AN is for "Total wait time". Therefore I am putting the formula in column AN however it could be moved if needed.

Update of tracking sheet automated 061015-1.xlsx

Appreciate the feedback. If the original question has been answered, please mark this thread as solved and post additional questions to a new thread.

[JeteMc]

Your 11:39 post showed up after I had posted at 11:52, so that solution will not address the column AR issue. Incidentally on the latest copy of the spreadsheet I have column AP is the last one that has a header, so I feel like the layout has changed, which is probably even more reason to start a new thread.

[doop4204]

JeteMc,

I found the isse. Yes. I changed something. I will post a new thread. Thank you for your assistance on this datasheet. Im always impressed with the staff at Excel form. You guys rock.