Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 94
  • Last Modified:

conditional format cells

Hello,

I have a cell C4 that has a HH:MM format time, that i need formated based on this criteria:

If AE5="S" and b4 is blank and c4 is past the time value in AE3, then make cell red
If AE5="S" and b4 is blank and c4 is not past the time value in AE3 but is within 60 minutes of the time value in AE3, then make cell yellow.
If AE5="S" and b4 is blank and c4 is is not past the time and greater than 60 minutes of the time value in AE3, then no fill
If AE5="S" and b4 is not blank, then make cell black.
0
macone1976
Asked:
macone1976
1 Solution
 
fabriciofonsecaCommented:
Please find the solution in the attached file.

As you did not mention the cell yo want to format, I used "AJ"

Regards,
Book2.xls
0
 
macone1976Author Commented:
Thanks, the one issue is there is data already in the cell so with the formula it would overwrite my data and place the formula data in there.
0
 
fabriciofonsecaCommented:
you can just copy the cell and past in any other you want.

Anyway... if you have problems with this just tell me which cell would you like to have the formula and I'll send you a sample file with the formula / flag color in this cell.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
pony10usCommented:
@fabriciofonseca:

"I have a cell C4 that has a HH:MM format time, that i need formated based on this criteria" would indicate they want cell C4 to be formatted based on the given criteria.  

That was the part that was stumping me.  If you input a value in C4 and you use black as one of the colors then you would need to also account for font color if you want to be able to see the value.
0
 
fabriciofonsecaCommented:
ops...

  Please find attached the updated file with the C4 cell as the color flag.

  I am using the cell "IS1" as support for this solution, therefore this cell cannot be used for other purposes.

  Does it solve? Is there any cell I could use as a "support cell" in case the "IS1" is already used?

Regards,
Book2.xls
0
 
Martin LissRetired ProgrammerCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now