Trying to high a date within a cell if it falls within a time window preceding a due date.

[dn7309]

Hello everyone, I'm trying to figure out the formula for getting a cell with a date to highlight a certain color if it happens to fall into a 12 month window of another date in a another cell.

For example... In the screen below person from A through F have their own due date and their last completed assignment date.

Excel.jpg

What I want to do is the following:

1. If the person completed the assignment within 12 month window preceding their due date, I want the cell to highlight green.

2. If the person completed the assignment outside 12 month window preceding their due date - but the due date has not passed yet, I want the cell to highlight yellow

3. If the person completed the assignment outside 12 month window preceding their due date - but the due date is passed, I want the cell to highlight red.

Well my initial attempt was to use the conditioning format and use the between (for the first condition), less than (the second condition) and greater than function (for the third condition). Even if that was correct, I has a hard time applying it to the entire spread sheet where the everybody's date is different. So once I figure out the formula how I make it condition will only apply correctly to everyone based on their specific due date?

this is by the the hardest excel worksheet I ever done so any any help will be appreciated.

[[email protected]]

Yes, this is possible.

You need to attached excel sheet with sample data.

Regards,
Suhas

[protonLeah]

The syntax for DATEDIF is as follows:

=DATEDIF(Date1, Date2, Interval)

Where:
Date1 is the first date,
Date2 is the second date,
Interval is the interval type to return.

If Date1 is later than Date2, DATEDIF will return a #NUM! error. If either Date1 or Date2 is not a valid date, DATEDIF will return a #VALUE error.
The Interval value should be one of
Interval Meaning Description

Please Login or Register  to view this content.

[gak67]

You don't need a datedif formula, which is an unsupported function within excel

For the yellow, as a formula within conditional formatting:

Please Login or Register  to view this content.

For the green, as a formula within conditional formatting:

Please Login or Register  to view this content.

For the red, as a formula within conditional formatting:

Please Login or Register  to view this content.

[dn7309]

Yes, this is possible.

You need to attached excel sheet with sample data.

Regards,
Suhas

Here is my sample date sheet. let me know what you came up with.

[dn7309]

You don't need a datedif formula, which is an unsupported function within excel

For the yellow, as a formula within conditional formatting:

Please Login or Register  to view this content.

For the green, as a formula within conditional formatting:

Please Login or Register  to view this content.

For the red, as a formula within conditional formatting:

Please Login or Register  to view this content.

I tried plugging your formula into my sample spread sheet and it seem to highlight all cell red. I even changed the date to see if it fits the requirement and the color does not change accordingly. For example, in my sample spreadsheet I changed Anthony's Assignment completed date to 05/13/2015 to fit the green requirement, the cell turns red. When I changed the date to 5/13/2014 fit the yellow requirement, the cell turned red. And as for the red Is it necessary to plus in a "today ()" function to it, otherwise, how would excel know if we have already reach the due date already?

[gak67]

My apologies. I had the forulas the wrong way around. Try these instead:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

See attached file. I have changed your due dates so you can see it working as your sample data as loaded is all yellow.
Assignment Tracking.xlsx

[dn7309]

Thanks. And when you try to apply this to everybody with differ due date, does the format painter work or since everybody's duy date is different I have to format them individually?

[dn7309]

My apologies. I had the forulas the wrong way around. Try these instead:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

See attached file. I have changed your due dates so you can see it working as your sample data as loaded is all yellow.
Attachment 337048

Thanks you took care of the green and yellow conditions, but I still cannot get the red function to work correctly. I want anyone who has not completed their assignment to turn red. So if a person completed their assignment two years ago (outside the 12 month window between), but the due date is say 04/01/2015 I like to have it turn red indicating that the person is overdue when the calendar hits 04/1/2015 or beyond.

So for example, a person have a 12 month window that went from 04/1/2013 to 04/1/2014, and he last completed his assignment on 02/1/2013. Now that 04/1/2014 has come and go, he is overdue and he needs to be in the red.

But it look like you nailed the green and yellow, Thanks a lot.

[gak67]

Sorry, I misunderstood what you meant by their due date having passed. I thought you meant they completed their assignment after their due date. Try this version: Assignment Tracking (2).xlsx

[dn7309]

That WORKED! Thanks a lot. I need to marked thread with solved now

[gak67]

And to answer your earlier question, you can copy and paste, or use the format painter, and the conditional formatting will apply to the new cells.