Tuesday, April 8, 2014

yogi_Apply Conditional Formatting To Columns A Through D If Entries In Rows Of Column B Are Not Successively 'Hi' And 'Bye'


                                         Google Spreadsheet   Post  #1592
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-08-2014
post by Bryan P -- question by J Viscardi: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/OsjqOTXZQl4)
New Conditional Formatting option - Custom formula is
Hi Yogi, 

    I'm wondering if you can help me implement a conditional formatting concept.

    I have a spreadsheet that tracks employee hours via an email sent to my email address. Subject "Hi" for In and "Bye" for Out.  This information is pulled from gmail and put into a google sheet row.  I would like to create a condition where the background will turn RED should an employee clock in but forget to clock out. Or, should they forget to clock in but remember to clock out.  

Here's what I imagine:
If("user" & "Hi" on "date" ) and (no("user" & "Bye") on same date) then turn background red
ex. Color Background RED: IF(A2:A, A2:A, "John", B2:B, Hi, 4/8/14) and NO(A2:A, A2:A, "John", B2:B, Bye, 4/8/14) 

Thank you for your expertise.

John

Spread Sheet Example
-------------------------------------------------------------------------------------------------------------------------------------