Conditional Format Date when within a certain range of dates

Question

I am having trouble coming up with a conditional format rule that works.  I would like to have a rule that looks at the date in column C and if the date falls between the first of the current month and the 8th day of the current month then the cell format should be Yellow.  I have two current rules for those cells that says that if the date is less than Today then the cells are Red and the second rule says that if the date is Today + 14 then the cell is Yellow.  The purpose of Column C is to show the due date for a certain training task.  If the date is less than Today then it is overdue and should show Red.  I have the rule to make the cell Yellow if it is within 14 days of Today so that we know that training is coming due.  For this one specific training event there is a waiver for the first 8 days of the month - so even when the date falls in that date range and it would normally be Red for being less than Today I want it to show Yellow during those 8 days and then if the current date is the 9th of the month is would show Red if the event was not accomplished.  If the training event is accomplished for the month then it would show the first day of the next month.  In the example below only C11 would have no fill for the format because he is current for his training and is not due until 5/1/2017.  Cell C5 is the only one that should show Red because the member is overdue.  The rest of the members should show yellow because they are still in the 8 day grace period.  Once the date in A1 shows 4/9/2017 then if the date in column C is still 4/1/2017 then they should show red.  Please let me know if you are able to help me with this.  This is something that will go on every single month so it is not specific to the month of April.  Thank you in advance!

	A	B	C
1	4/2/2017
2	Current Date		GG30
3	Last Name	First Name	training due date
4	WATERS	HAL	4/1/2017
5	LUPES	JOHN	3/1/2017
6	WHITE	JIM	4/1/2017
7	DOE	JAMES	4/1/2017
8	MORTON	HANK	4/1/2017
9	SCHOON	BILL	4/1/2017
10	TOMS	KEENAN	4/1/2017
11	BLUE	MARK	5/1/2017
12	GREEN	MIKE	4/1/2017

Answer 1

Perhaps you want

=AND(C4<TODAY(),C4>=TODAY()-8)

Answer 2

Select the dates; I'll assume that C4 is the active cell within the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

=AND(C4<$A$1,DAY($A$1)<=8,C4-DAY(C4)=$A$1-DAY($A$1))

Click Format...

Activate the Fill tab.

Select yellow, then click OK twice.

Answer 3

You can use

=AND(C4<TODAY(),DAY(TODAY())<=8,C4-DAY(C4)=TODAY()-DAY(TODAY()))

What do you want to happen if the due date is the last of the month? Please explain in detail.

Answer 4

Thanks HansV, I believe this will work perfectly.  I'm having a hard time understanding exactly what this formula is doing to make this calculation which is why I am asking some follow up questions.  I see that I can make easy changes like if I wanted 4/1 to be formatted I would just change the beginning to be formula to C4<= instead of C4= and if I want to change the number of days in the grace period I would just change the 8 to something else. But when I try to change the due date scenario I run into some trouble.

For the scenario that the due date is the last day of the month I would like to see the same thing happen.  So say the due date is 3/31/2017.  I would want the formatting to turn yellow if the member has not updated their training by the 8th day of the following month.  So the only difference is that in the other scenario the due date was 4/1 and in this scenario it is 3/31.  I tried changing the due date in one of the cells to 3/31 to see if it would work the same way and it did not and I don't understand exactly what the formula is saying so I can't figure out what would need to be changed if I wanted to have that happen for a date that was different than the beginning of the month, in his example the last day of the month.  When I change the due date to 3/31 and the current date to 4/2 (or any day between that and the 8th day) the formatting doesn't update at all for the cell.

Answer 5

Thank you! This works in the scenario I laid out.  I do have two additional questions.  What if I had a different due date, say the due date was the last day of a month instead of the first day - what would I need to change to make this work?  The second question is would I be able to use TODAY rather than referencing cell A1 for the current date? Right now A1 populates from another tab and is based on the date of an output file.  There could be a scenario where the output file is stale and the date may not actually represent the current day so it may be better to use TODAY in the formula if that is possible.