I need a cell to contain multiple formulas and highlight

[kfromtexas]

I am new to an online forum and with using excel. I have gotten some books, you tube, google but can not find exactly what I am looking for.

I will put an example of my spreadsheet up (once I figure out how to do that).

Okay so this is the formula I have
=MOD(E4-D4,1)+AND(C4="Critical",F4=>00:20,F4=00:20)

This is what i am trying to do:

If C4 says "Critical" and cell F4 is greater than 20 mins. I want cell F4 to automatically highlight in red.

C4 contains a validation drop down box and cell F4 contains the formula =MOD(C4-D4,1)

Basically this is to keep track of help desk tickets for an IT dept. I need my spreadsheet to show the status of the ticket, the time it was issued, the time it was closed, and in Column F it will show the time the tech spent working on it.

If the status is Critical and the ticket has been open for more than 20 mins i want the cell with the time spent working to automatically turn red after the ticket has been open for 20+ mins.

I hope this makes sense. I could use tons of help! I know i am not good at excel I'm one of those play around self teach and can't figure out how to do this.

If anyone knows how to do this i would be grateful or if you just have any references I could look at that would be awesome too!

I have been watching ExcelIsFun on you tube he said I should post my question with you guys!

Thanks anyone, everyone!

 

[T. Valko]

I am new to an online forum and with using excel. I have gotten some books, you tube, google but can not find exactly what I am looking for.

I will put an example of my spreadsheet up (once I figure out how to do that).

Okay so this is the formula I have
=MOD(E4-D4,1)+AND(C4="Critical",F4=>00:20,F4=00:20)

This is what i am trying to do:

If C4 says "Critical" and cell F4 is greater than 20 mins. I want cell F4 to automatically highlight in red.

C4 contains a validation drop down box and cell F4 contains the formula =MOD(C4-D4,1)

Basically this is to keep track of help desk tickets for an IT dept. I need my spreadsheet to show the status of the ticket, the time it was issued, the time it was closed, and in Column F it will show the time the tech spent working on it.

If the status is Critical and the ticket has been open for more than 20 mins i want the cell with the time spent working to automatically turn red after the ticket has been open for 20+ mins.

I hope this makes sense. I could use tons of help! I know i am not good at excel I'm one of those play around self teach and can't figure out how to do this.

If anyone knows how to do this i would be grateful or if you just have any references I could look at that would be awesome too!

I have been watching ExcelIsFun on you tube he said I should post my question with you guys!

Thanks anyone, everyone!

What version of Excel are you using?

That formula doesn't make any sense!

Shouldn't that be 2 formulas?

You probably want this formula for the time difference:

=MOD(E4-D4,1)

And to get the cell to change red you'd want to use a formula like this in conditional formating:

=AND(C4="Critical",F4>=TIME(0,20,0))

 

[kfromtexas]

HAHA! I know none of what I wrote made sense probably b/c I barely know what I am talking about.
Like I said I kinda just play trail and error to figure it out.

I am using 2007.

I put =AND(C4="Critical",F4>=00:20) in the new formatting rule box with the use a formula to determine which cells to format.

But it wasn't working I used 00:20 because that's the way the time was set up if column F

I'm gonna try it though and let ya know if it worked!!!! thanks so much!!

 

[kfromtexas]

Yes that worked you are so awesome! I just didn't know how to enter the time part! thank you so very much If you were here I would hug you!

 

[T. Valko]

Yes that worked you are so awesome! I just didn't know how to enter the time part! thank you so very much If you were here I would hug you!

I kinda just play trail and error to figure it out.

That's pretty much how I do it, too! It's just that I've been doing it for a "few" years!

Thanks for the feedback!

 

[kfromtexas]

Hey Valko... can you help me??????

I had to change up the formula i was using in column F for the time and now the conditional formatting that I had for that column no longer works. It just highlights anything over 20 mins instead of recognizing the condition that cell C also has to say "Critical"

so, using this in the F cell =IF(COUNT(D5:E5)=2, E5 - D5 + (E5 < D5), "") and using

this for CF in the F cell =AND(C4="Critical",F4>=TIME(0,20,0)) (which turns the F cell red, does not work) now any time the entry in F is over 20 mins or there is nothing the F cell is blank, it highlights red???

Why what is it I need to change???

Does this make sense???

 

[T. Valko]

Hey Valko... can you help me??????

I had to change up the formula i was using in column F for the time and now the conditional formatting that I had for that column no longer works. It just highlights anything over 20 mins instead of recognizing the condition that cell C also has to say "Critical"

so, using this in the F cell =IF(COUNT(D5:E5)=2, E5 - D5 + (E5 < D5), "") and using

this for CF in the F cell =AND(C4="Critical",F4>=TIME(0,20,0)) (which turns the F cell red, does not work) now any time the entry in F is over 20 mins or there is nothing the F cell is blank, it highlights red???

Why what is it I need to change???

Does this make sense???

You have to make sure F4 actually contains a time entry.

Use this as the CF formula:

=AND(C4="Critical",COUNT(F4),F4>=TIME(0,20,0))

 

[kfromtexas]

ahh your awesome i didnt even pay attention to that. Thanks Valko!!!

 

[T. Valko]

ahh your awesome i didnt even pay attention to that. Thanks Valko!!!

You're welcome. Thanks for the feedback!

 

[kfromtexas]

Hey Valko...

I have another workbook that has me in over my head... many people have looked at it one person gave it a stab usign macro.. but I think I iwll need to use some sort of index array if ya have tim elet m eknow if you think you can give it a quick look if it is somehting you may be bale to help with! Hope you are doing good!

Oh, tried to message/email ya but ...yeah can't lol sorry for reaching out this way hope I'm not bugging ya!