EXCEL 03 - How to Highlight Cells Based on Date Thresholds Before Events?

A question for Excel 2003 magicians

I have a customer base.
I need to make an alarm go off for me - e.g. highlighting this person with a color, if:

1) today's date retrieved with the function

is 7 days less than the date of birth. So 7 days before the birthday of a given person, I need to know about it :)

2) as above, but a reference to a different date and here I need to know 60 days before that date.



And it would be great if this distinguished person appeared on the page from the menu to my database (first tab). Because my database consists of many tabs.


Thank you in advance.

In the first case, you can apply conditional formatting where the remainder of the division of years will be zero.
Formula:
= IF (MOD (YEARFRAC (E2, (TODAY () + 7) * 1), 1) = 0, TRUE, IF (MOD (YEARFRAC (E2, (TODAY () + 6) * 1), 1) = 0 ;TRUTH FALSE))
for 2 days (+6 and +7)

You can use the same formula in the second case using values other than TRUE AND FALSE, eg "Birthday in 7 days" etc. in combination with VLOOKUP or as you see fit.
--------------
Edit

You can also combine the MONTH AND DAY functions:
= IF (AND (MONTH (E2) -MONTH (TODAY ()) = 0; OR (DAY (E2) -DAY (TODAY ()) = 7, DAY (E2) -DAY (TODAY ()) = 6, DAY (E2) -DAY (TODAY ()) = 5)), TRUE, FALSE)
for 3 days.
Formula:
= IF (AND (MONTH (E2) -MONTH (TODAY ()) = 0; OR (DAY (E2) -DAY (TODAY ()) = 0)), TRUE, FALSE)
for 7 days.
Whereas the formula:
= IF (AND (MONTH (E2) -MONTH (TODAY ()) = 2, DAY (E2) -DAY (TODAY ()) = 0), TRUE, FALSE)
will return 'TRUE' 2 months before the date in E2
Or use a VBA using a function DatePart .

adamas_nt - I used this formula:


And I get a true cell if my client has a birthday in 7 days.

Now how do I make this client stand out?

Apply conditional formatting. Select the first cell of the range (columns), from the menu choose: Format> Conditional formatting and as a condition choose: 'Formula'. Paste the above-described one into the formula window and set the cell formatting as desired. Then right-click the formatted cell pm, select copy, select the range (column with names) in which the formatting is to work and select: 'paste special> Formats'. You will not "lose" the names entered in this way.

However, in a cell in the formula you can change: TRUE; FALSE to e.g. "Birthday in 7 days"; ""

I will step on this topic.
I am looking for a solution for excel to highlight cells with a date lower than today by, for example, a month.
I've tried in various ways, but haven't managed to set it up yet.
The point is that I set a review date and that a month earlier, excel would highlight this date for me with some color (e.g. yellow), and if it was reached or exceeded, it would also change to the next color (e.g. red).

Thanks in advance for any suggestions.

Pawel P. wrote:

Thanks in advance for any suggestions.

As you can see all the suggestions are above :)

You can also yes
create two conditions in conditional formatting (see above how)
typing in the first

in the second
[code:1:75df8f7e3c]=A1-DZIŚ()

This is how it works, only in the second condition it must be>, not = 0

Thanks

I will connect to the topic. How to make selected cells highlighted on a specific day every month. Let's say two days before the date in yellow and the day of the date in red. And one more question, is it possible for the red underline to disappear after clicking on it?

m-haus wrote:

I will connect to the topic. How to make selected cells highlighted on a specific day every month. Let's say two days before the date in yellow and the day of the date in red.

Do as described above.

m-haus wrote:

And one more question, is it possible for the red underline to disappear after clicking on it?

It is possible, but only through a macro.
I propose (without a macro) to extend the conditional formatting with an additional condition in the style that if in the next cell there is the word "I KNOW" then the format returns to white.
Of course, then, instead of clicking on the cell, write "I KNOW" in the next cell.

I will connect to the topic.
I'm sorry in advance, but I am asking for the 2007 version
I have a fleet of company cars and I did highlighting in Excel 30 days before the end date of the inspection and insurance, but can it be additionally done so that when the car is highlighted, information appears in the tasks in the Outlook program?
best regards

I attach a ready file with formulas.

green color not older than 90 days
yellow older than 90 days but not older than 180 days
red color older than 180 days

I am probably duplicating the thread, but despite my sincere intentions and the time spent with advice on the forum and excel, I can't figure out a rule. I wanted to make it easier for myself to check the payment deadlines for invoices, so I created a table as in the attachment. I would like to include 2 elements in it.
1) the cell with the invoice amount would become red if the due date has passed
2) if I entered a date in the column for payment for VAT, the cell with the amount would change to green.

Please support me

The answer to Mrs. meyouve's problem.
You need to select cell F2 and use conditional formatting (in Excel 2003 Format / Conditional Formatting) and enter conditions for formulas:
Condition1: = ISNUMBER (H2)
and format the background green. The invoice amount will turn green when a number is entered in the H column (it is not easy to verify that it is a date).
Condition2: = AND (NOT (ISBLANK (G2)); G2

Hello
I need a formula that will highlight in red, as the date in A2 will be 7 days earlier than = TODAY () today is e.g. G5

so as in A2 it will be April 22, 2014, cell A1 will be in red.

In cell A1, conditional formatting according to the formula:

if the red color is to appear for dates 7 days before the current date and remain so in the following days, or

if the red color is to be only for 1 day for dates 7 days before the current date.

darwaw007 wrote:

I attach a ready file with formulas.

green color not older than 90 days
yellow older than 90 days but not older than 180 days
red color older than 180 days

please help with this formuka I can't get to that

apple99 wrote:

please help with this formuka I can't get to that

What can't you come with?

Select a color formatted cell or an entire column where the cells are formatted, press conditional formatting and rule management ...
You will see all condition formulas.