I'm really having a hard time to if someone could help me out, pls. From Arrival time data, alarm me ( color code) if that person stayed more than four hours against real time. This will be use for our ED so that we will know if the patient is already "long-stay" ( more than 4 hours)
-
Welcome to Super User. We are not a script writing service. We expect users to tell us what they have tried so far (including any scripts they are using) and where they're stuck so that we can help with specific problems. Questions that only ask for scripts are too broad and are likely to be put on hold or closed. Please read How do I ask a good question?.– DavidPostill ♦Commented Dec 3, 2015 at 23:02
-
You should also edit your question to include some example (mock) data. See Format Text as a Table for a web utility that will help you to create a nice data table you can paste into your question.– DavidPostill ♦Commented Dec 3, 2015 at 23:04
-
if I understand what you are saying, you want something that automatically alerts you when a time threshold has been reached per patient. Is this correct? If so, I don't think Excel has anything out-of-the-box to do that. At the very least, you have to refresh your formulas.– rfportillaCommented Dec 3, 2015 at 23:34
-
Anything that relies on the colour of a cell is going to require VBA. This is almost always a bad design. Use values to represent data (even if this is Yes/No) and then use conditional formatting if necessary to highlight that. Once you have explained what you are actually trying to do, you might get some great answers.– AdamVCommented Dec 4, 2015 at 12:08
Add a comment
|
1 Answer
Based on a cell containing the patient's arrival time and date (e.g. 03/12/2015 23:25) you can use conditional formatting:
- Select Conditional Formatting > New Rule...
- Select "Format only cells that contain"
- Select "less than" from the dropdown list and use the equation "=NOW()-(4/24)" (without the quotes)
- Select the formatting you would like to apply (e.g. red text) by clicking the "Format..." button
You can then used the Format Painter to extend this formatting to other cells and F9 to recalculate the sheet and update the formatting.
-
This is based on the 4hours you mentioned as that is (4/24) of a day. Commented Dec 3, 2015 at 23:34
-
Thanks! What about formula for calculating elapsed time against real time? Thank u so much! Commented Dec 4, 2015 at 11:40
-
You could have an additional column that uses a formula. For example if arrival Date / Time was in cell A6, you could enter the formula "=24*(NOW()-A6)" into B6. You could then copy the formula down the column. This gives the elapsed time in hours. You will likely have to reformat the cell away from being in Date format (select Format Cells > Number > "Number") before you copy the cell down the column. You could then apply the conditional formatting to this column based on >4. Commented Dec 5, 2015 at 10:20
-
If you want an alarm to sound or pop-up you'll need to use VBA I think. Take a look at this: pcreview.co.uk/threads/… Commented Dec 5, 2015 at 10:51