Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have two sheets:

  1. Users in their respective groups and
  2. A compilation of those same users with an expiration date.

I'd like to highlight any cell in the the first sheet if their respective date is less than the current day.

enter image description here

enter image description here

enter image description here

EDIT

Using tylyn's formula, I'm trying to add a secondary rule that compares today's date and the date listed in the table and if that number is less than 30 days, format a different color.

Here is what I tried: =VLOOKUP(A1,'Sheet name 2'!$A$1:$B$1000,2,FALSE)>TODAY()*TODAY()-VLOOKUP(A1,'Sheet Name 2'!$A$1:$B$1000,2,FALSE)<30

Also tried: =DATEIF(TODAY(),VLOOKUP(A1,'Sheet name 2'!$A$1:$B$1000,2,FALSE),"D")<30

FINAL EDIT

If I had used DATEDIF instead of DATEIF, it would have worked

share|improve this question

2 Answers 2

up vote 2 down vote accepted

To format the cells if their date is before the current day (Sheet 1 = groups, Sheet 2 = dates and names):

  1. Go to sheet 1, select all the data (A1:D7 in your example)
  2. Click Conditional Formatting > New rule > Use a formula to determine which cells to format
  3. Click the Format button and change the format to whatever you want (bearing in mind that this one is for 'before the current day'. Paste this:

=VLOOKUP(A1,Sheet2!$A$1:$B$15,2,FALSE)<TODAY()

Change the Sheet2!$A$1:$B$15 to whatever is right for your spreadsheet (this is correct for your example). Make sure you only put the $ in where they are above (they are for absolute references)


To format the cells if their date is within the next 30 days:

  1. Go to sheet 1, select all the data (A1:D7 in your example)
  2. Click Conditional Formatting > New Rule > Use a formula to determine which cells to format
  3. Click the Format button and change the format to whatever you want (bearing in mind that this is the one for dates 'upcoming in the next 30 days'. Enter this formula:

=DATEDIF(TODAY(), VLOOKUP(A1,Sheet2!$A$1:$B$15,2,FALSE), "d") < 30

This looks at the date dif ference between today and the ones on Sheet 2. If they are < 30 then it is true and formats the cells. The "d" is called the interval and determines whether to look for the difference between days/months/years etc. Make sure you only put the $ in where they are above (they are for absolute references)


The important thing now is to go to the Conditional Formatting button > Manage Rules > Show Formatting Rules for: This Worksheet. Click the second formula (it should be the one with DATEDIF and click the down arrow at the top right. This sets priority to your first formula.


Screenshots:

enter image description here enter image description here enter image description here This look a bit different because you had an incorrect date in your example (yadyr): enter image description here

share|improve this answer
    
Wow, I had it, except that I used DATEIF, instead of DATEDIF. Changing that fixed the issue. In your last screenshot yaer should be red (since the date has passed) but I have a theory on that. You mention yadyr was an incorrect date, why? Is it because out of 11/15/2014, you took 15 to be the month instead of the day? Just curious if Excel has regional differences (I am aware that day/month/year is common outside of America). –  MattSteelblade May 28 at 19:02
    
@MattSteelblade Ah... I'm from UK, so I am in the format dd/mm/yyyy. I didn't think about outside UK. So all you need to do is format your cells in the format mm/dd/yyyy. Go to format cells > Date > change the format (I'm not on my windows computer now, so I don't know if those are exactly the steps, but you'll be able to figure it out! Strangely, I didn't even see your 'ALSO TRIED', I only saw the first edit, and I don't even know what you did there, so I added my own answer... But yes, all you needed to do was say DIF not IF. Please +1 if you found my answer helpful :) –  ṧнʊß May 28 at 19:23

Assuming the groups are showing on Sheet1, the list of names and dates is on sheet 2:

  • select the table with the groups, including blank cells, starting in A1
  • click Conditional Formatting > New Rule > Use a formula to determine
  • enter the formula posted below, click the Format button and select the fill

Formula:

=VLOOKUP(A1,Sheet2!$A$1:$B$1000,2,FALSE)<TODAY()

Adjust the range of the lookup table to suit your data in sheet 2.

Note that there are no $ signs in the first A1 reference. This is important. If the top left cell of the selected table is not A1, you need to use the cell address of the top left table that you have selected.

This way the conditional format will look at the current cell, even if the format is extended / copied to other rows or columns.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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