Solved how to change the color for a formula time out of range

October 21, 2014 at 13:15:28
Specs: Windows XP
I have calculations of "onset to.." different times and need to differentiate good from bad times (by green and red coloring). The calculation works fine but my "bad" numbers are still green. Where do I go in Excel 2007 to set the time frames I want for each color?

See More: how to change the color for a formula time out of range

Report •

✔ Best Answer
October 23, 2014 at 12:01:51
Try these new formulas, they are a modification of the
old tried and true one I first suggested.

I've done some limited testing and they seem to work.
They also work going across midnight, which was the glitch that popped up in the last ones.

So with your data like:

           A          B          C          D       E
 1)  Door Time   Stroke Nurse   CT     CT Result   tPA
 2)     17:00       17:09      17:20     17:40    17:50

20)     18:00       18:09      18:20     18:40    18:50                                                   

30)     19:00       19:09      19:20     19:40    19:50

For Stroke Nurse select your range of cells > B2,B20,B30

New formula:

=IF(B2<>"",TEXT(IF(A2>B2,B2+1-A2,B2-A2),"[H]:mm:ss")>"0:15:00","")

In the above, were using 15 minutes

For CT, select your cell > C2,C20,C30

=IF(C2<>"",TEXT(IF(A2>C2,C2+1-A2,C2-A2),"[H]:mm:ss")>"0:25:00","")

In the above, were using 25 minutes.

For CT Results, select your cell > D2,D20,D30

=IF(D2<>"",TEXT(IF(A2>D2,D2+1-A2,D2-A2),"[H]:mm:ss")>"0:45:00","")

In the above, were using 45 minutes.

and finally for tPA select your cell > E2,E20,E30

=IF(E2<>"",TEXT(IF(A2>E2,E2+1-A2,E2-A2),"[H]:mm:ss")>"1:00:00","")

In the above, were using 1 Hour.

Note the format in the formula is [H]:mm:ss
so if you plan to add more formula,
your time must be in that format.

MIKE

http://www.skeptic.com/



#1
October 21, 2014 at 16:14:51
It sound like you want to color cells either after a due date/time
or for an upcoming date/time.
Without more specific into, like Row Number & Column Letters and what formulas your currently using, I can only suggest looking at these two How-To's
they deal with only Dates, but may be of help:

Past Due Dates:
http://www.computing.net/howtos/sho...

Deadline Dates:
http://www.computing.net/howtos/sho...

MIKE

http://www.skeptic.com/


Report •

#2
October 22, 2014 at 09:03:09
My formulas calculate times from door to: CT, decision ,and drug when giving tPA to stroke patients. There are national timeframes we're measured against. If it's under the limit- the time needs to show green, over the limit-red. So the formula includes subtracting one row time from another row in a column to get the time as well as setting it such that under the limit it is green, over the limit it's red. Right now they all show green regardless of the length of time. Thanks for suggestions on how to fix it.

Report •

#3
October 22, 2014 at 09:57:59
What are your time limits?

How is the data entered into a cell,
as just time or as a date/time combo?

If your Time crosses over Midnight, it may cause you a bit of grief, so may I suggest you use a formula like:

=IF(A1>B1 , B1+1-A1 , B1-A1)

The "+1" in the formula causes Excel to treat B1 as if it were in the next day,

Also, this will return a Time,
if you would prefer your results in a decimal format then simply multiply by 24:

=IF(A1>B1,B1+1-A1,B1-A1)*24

     A             B              C
1) 10:30:00	14:00:00	3:30:00   =IF(A1>B1,B1+1-A1,B1-A1)
2) 10:30:00	14:00:00	3.50       =IF(A1>B1,B1+1-A1,B1-A1)*24

Cell C1 is formated as Time
Cell C2 is formated as Number

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
October 22, 2014 at 10:28:28
Thanks, Mike, for the quick answer. It's just time entered. What tab would I go to in Excel to set colors for the times elicited? I've looked in Data and Formulas and can't find anything that helps.

Report •

#5
October 22, 2014 at 10:47:37
Did you read the suggested HOW-TO's?

Your looking form Conditional Formatting, which is on the Home Tab.
The HOW-TO's give a step by step description on how to apply Conditional Formatting.

Without knowing what your time limits are,
I can only suggest you replace the Date formulas
with your Time formula and see how it works.

So you could use something like:

=IF(A1>B1,B1+1-A1,B1-A1)*24>3.5

Select a Red color and every cell that calculates over 3.5 hours will turn Red.

MIKE

http://www.skeptic.com/


Report •

#6
October 22, 2014 at 11:58:47
Mike, I read the how to's and used Conditional Formatting to change the boxes in question. Not sure if it will follow through to the next tabs in the file, but you've been a great help. Thank you!

Report •

#7
October 22, 2014 at 12:12:11
next tabs in the file

The Conditional Formatting will only work on the sheet where it is applied,
there are work arounds, but will need more info on how your workbook is
arraigned, otherwise simply copy the same CF rules over to the next sheet.


Also, there are other formulas that may work better for you,
but I need to know what your time frames are?
Do you need only Hours,
or do you need to break it down to Minutes
do you need to break it down to Seconds?

The more information you supply, the better we can assist.
I can't see your worksheet from where I'm sitting,
so you need to paint the picture.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#8
October 22, 2014 at 13:33:43
The time is expected to be only minutes, but could be hours and minutes - so the blank template lists 0:00 until the data is entered and the math occurs. Some of my times are: (there are actually 13 total times noted)
"door", which is time of arrival to ED doc time (hopefully less than 10 min.), door to Stroke RN arrival (hopefully <15 min.),
door to CT time (<25 min.)
door to CT results time (< 45 min.)
....ultimately door to tPA (hopefully < 60 min.)
If it's longer than that, bummer! My sheet allows for 3 patients per page, then I just add another tab for the next 3. And I do a separate report for each month. Do you see my painted picture? Terry

Report •

#9
October 22, 2014 at 16:04:20
OK, all your times as calculated off the Door Time so you have something like:

           A          B          C          D       E
 1)  Door Time   Stroke Nurse   CT     CT Result   tPA
 2)     17:00       17:09      17:20     17:40    17:50

20)     18:00       18:09      18:20     18:40    18:50

30)     19:00       19:09      19:20     19:40    19:50

With only 3 patients per page, you will need to select each group of cells for each Conditional Formatting Rule.

IE: Patient 1, Door Time is A2,   Stroke Nurse B2
    Patient 2, Door Time is A20,  Stroke Nurse B20
    Patient 3, Door Time is A30,  Stroke Nurse B30

For Stroke Nurse you would begin by selecting your range of cells.
IE you would select B2,B20,B30

This is a different formula and you may find it easier to work with:

=B2>A2+TIME(0,15,0)

I have substituted the TIME() function whose format is TIME(Hours,minutes,seconds)

In the above formula we are using 15 minutes.
So if the Nurse time (B2) is greater then the Door Time (A2), plus 15 minutes, we color it Red.

For CT, we select the three CT time cells, IE C2, C20, C30
and the formula would be:

=C2>A2+TIME(0,25,0)

In this formula we are using 25 minutes.
So if the CT time (C2) is greater then the Door Time (A2), plus 25 minutes, we color it Red.

Just make sure, you have the correct range of cells selected in the Conditional Formatting for each time point.

You can do that by selecting CF and
then the Manage Rules selection at bottom of popup window.
There is a small drop down box at the top next to:
Show formatting rules for:
change it to show the entire workbook.

Look at the Applies to: box and make sure you have the correct range of cells for each rule.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#10
October 23, 2014 at 07:59:04
Sorry to say but the new formula DOES NOT WORK

It suffers from the crossing Midnight problem that I spoke off in my earlier reply.

So go back to the tried and true formula of:

=IF(A1>B1,B1+1-A1,B1-A1)*24>"Your Decimal Time Limit Goes Here"

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#11
October 23, 2014 at 08:48:55
Thanks, Mike, for your persistence on the formulas. I'll give it a try today after I've seen patients and done a couple of other things. You have been a great help to me. The Admin. Sec. that used to do these things left, and there is no replacement, so it's good to know I have resources for issues like this. Terry

Report •

#12
October 23, 2014 at 12:01:51
✔ Best Answer
Try these new formulas, they are a modification of the
old tried and true one I first suggested.

I've done some limited testing and they seem to work.
They also work going across midnight, which was the glitch that popped up in the last ones.

So with your data like:

           A          B          C          D       E
 1)  Door Time   Stroke Nurse   CT     CT Result   tPA
 2)     17:00       17:09      17:20     17:40    17:50

20)     18:00       18:09      18:20     18:40    18:50                                                   

30)     19:00       19:09      19:20     19:40    19:50

For Stroke Nurse select your range of cells > B2,B20,B30

New formula:

=IF(B2<>"",TEXT(IF(A2>B2,B2+1-A2,B2-A2),"[H]:mm:ss")>"0:15:00","")

In the above, were using 15 minutes

For CT, select your cell > C2,C20,C30

=IF(C2<>"",TEXT(IF(A2>C2,C2+1-A2,C2-A2),"[H]:mm:ss")>"0:25:00","")

In the above, were using 25 minutes.

For CT Results, select your cell > D2,D20,D30

=IF(D2<>"",TEXT(IF(A2>D2,D2+1-A2,D2-A2),"[H]:mm:ss")>"0:45:00","")

In the above, were using 45 minutes.

and finally for tPA select your cell > E2,E20,E30

=IF(E2<>"",TEXT(IF(A2>E2,E2+1-A2,E2-A2),"[H]:mm:ss")>"1:00:00","")

In the above, were using 1 Hour.

Note the format in the formula is [H]:mm:ss
so if you plan to add more formula,
your time must be in that format.

MIKE

http://www.skeptic.com/


Report •

Ask Question