1

Please help me out with this, in the given table I want to apply conditional formatting based on following procedure:

1st - Get the day based on date

2nd - Then compare the value entered in particular cell, say D2, with respective quota for the day(Mon, Tue, Wed, Thur - Weekdays and Fri, Sat, Sun - Weekends) assigned to the person

3rd - If he exceeds quota then cell colour to be changed to "red"

Table link - https://docs.google.com/spreadsheets/d/1CWm3vdxvMIoxoq0ZlA3Y9M5_tXEtuWKm-iP0RR7pEQ0/edit?usp=sharing

  • Are you using the webversion of google spreadsheet to show us the table? And are you using Excel application on your computer? – RamonRobben Feb 27 '17 at 10:50
  • What did you try? – Dave Feb 27 '17 at 11:56
  • @RamonRobben - Yes, for actual work I am using Excel 2013. – Sarvesh Feb 28 '17 at 6:13
  • @Dave - My weekend means Friday, Saturday and Sunday. I tried few formulas but none of them worked. – Sarvesh Feb 28 '17 at 6:15
0

I broke down your question in steps and I solved them one by one. First I tried to get a value TRUE or FALSE based on if the date is in the weekend or not.

=IF(IF(WEEKDAY(DATECELL) = 6; TRUE; IF(WEEKDAY(DATECELL) = 7;TRUE;FALSE));"TRUE";"FALSE")

TRUE == Weekend

FALSE == Weekday

After that I could write the conditions for what to check. In your case if the value is greater then in the quota. We can use this in our final condition for 1 cell. Unfortunatly its very difficult to use conditional formatting for this since I have to use alot of static data I'm not sure if that can be changed but at least I got this for now and it works.

=IF(IF(WEEKDAY(DATECELL) = 6; TRUE; IF(WEEKDAY(DATECELL) = 7;TRUE;FALSE));IF(CURRENTCELL > WEEKENDS;TRUE;FALSE) ; IF(CURRENTCELL > WEEKDAYS;TRUE;FALSE))

Replace words such as:

  • Word == With what to replace it
  • CURRENTCELL == The cell where you are going to apply the condition in.
  • DATECELL == The cell where the date is located in
  • WEEKENDS == Cell where quota is set for weekends
  • WEEKDAYS == Cell where quota is set for weekdays

Maybe you could try and figure out if we can get cells relative from the position of the condition. If we can do that we can say from our row C go 2 down to get to the quota of the weekdays.

I found an intreseting question about using something called indirect. Using twice the same cell with indirect function you could use this to get the static cells and make the condition work like you want them to work.

  • @Sarvesh if this is the solution you end up using please consider to mark it as answer to other people know this fixed it for you. – RamonRobben Feb 28 '17 at 7:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.