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?

✔ Best Answer

Try these new formulas, they are a modification of the

old tried and true one I first suggested.I've done some

limitedtesting 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:50For 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

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

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.

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)*24Cell C1 is formated as Time

Cell C2 is formated as NumberMIKE

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.

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

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!

next tabs in the fileThe 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

message edited by mmcconaghy

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

OK, all your times as calculated off the Door Timeso 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:50With 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,B30This 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

message edited by mmcconaghy

Sorry to say but the new formula DOES NOT WORKIt 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

message edited by mmcconaghy

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

Try these new formulas, they are a modification of the

old tried and true one I first suggested.I've done some

limitedtesting 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:50For 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

Ask Your Question

Weekly Poll

Do you think the EU antitrust ruling against Google was fair?

Discuss in The Lounge

Poll History