Highlight line if Homework dates in multiple columns are greater than Final date

**rslay** · 02-12-2016, 10:20 AM

The scenario is that I am teaching an online class that requires 4 homework submissions per semester with a final exam. I need help to easily find if a student turned in a piece of homework after their final exam.

In the attachment you will see that student 2's final exam was on 09/25/2014 and they turned in a piece of homework on 10/16/2014. I have highlighted this in the attachment, but I was wondering if there was a code to easily highlight or create a chart to pull out only the outstanding dates for every student all at once?

I used the example on the site below to create a chart that uses an INDEX MATCH formula:
http://thinketg.com/how-to-return-mu...ch-or-vlookup/

The formula works well, but the problem is that you have to manually change the student number each time. This is fine with a few students, but it takes a lot of manual effort when there are hundreds of students.

I am thinking there is a solution using a combination of VLOOKUP, COUNTIF, INDEX, MATCH, but I need guidance.

Please let me know if you are able to help.

**rcm** · 02-12-2016, 10:50 AM

This macro will do what is requested. There are enhancements that can be done to it like automate it when a date is inserted.

Please Login or Register  to view this content.

**newdoverman** · 02-12-2016, 10:50 AM

I suggest that you use a column for the date of the Final Exam and fill the column with this formula. Using your example the column for the Final Exam would be column G
Enter this formula in column G and fill down. This will enter the date of the final exam for each student as indicated in the chart

Formula:

Please Login or Register  to view this content.

Then use Conditional Formatting.
Select the data in columns F and G from F2 to the end and enter Conditional Formatting, New Rule, Use Formula... and enter this simple formula

Formula:

Please Login or Register  to view this content.

Choose the formatting that you want to indicate the late submissions.
In the Applies To field after you hit OK, make the entry to include column D like this =$D$2:$G$17 and click apply.

**newdoverman** · 02-12-2016, 11:15 AM

This version will also work without the G column.
Select the data D2:F17 and go to Conditional Formatting, New Rule, Use Formula and enter this formula.

Formula:

Please Login or Register  to view this content.

Select the formatting of your choice.

Highlight line if Homework dates in multiple columns are greater than Final date

LinkBack

Thread Tools

Rate This Thread

Display

Highlight line if Homework dates in multiple columns are greater than Final date

Re: Highlight line if Homework dates in multiple columns are greater than Final date

Re: Highlight line if Homework dates in multiple columns are greater than Final date

Re: Highlight line if Homework dates in multiple columns are greater than Final date

Thread Information

Users Browsing this Thread

Similar Threads

How to highlight multiple cells giving a sum greater than X

Formula to highlight dates on a calendar for multiple people scheduled for school dates

[SOLVED] Formula to highlight cell/data greater than a specific date

Highlight dates in column H if they are greater than column G by a certain number

[SOLVED] Highlight the Cell which is greater than Next Friday's Date

Excel 2007 : How to highlight a cell in Col. A, if the date in Col. B is greater than date in Col. C

greater control of date/time stamps on line charts in excel

Bookmarks

Bookmarks

Posting Permissions