How do I highlight important dates automatically in Numbers?

Hi ostoura,

Your question is related to the one in this thread, but the thread is more than 2 years old now, and Numbers has changed in that time. Please repost your question as a New Question.

You might want to clarify what you mean by "the rows at the end of every month."

Be sure to include the OS X/macOS version (Apple menu > About this Mac) and the version number of Numbers (Numbers menu > About Numbers) you are using.

Regards,

Barry

HI Samuel,

What you are looking for is called "Conditional Formatting". Here's an example using Numbers 2. It requires one rule for each condition, and you must be careful to order the rules so that the bottom one on the list is the first to become TRUE.

The rules shown are applied to all the cells in column B. Cell A1 contains the formula =TODAY()

In Numbers 3, Conditional Formatting should be found by tapping the paintbrush. Select all the cells in the Date column before applying the rules.

Regards,

Barry

Hi Samuel,

highlight a week before there active date in say green and then in red after the active date.

I see Barry has taken the "active date" to be today.

I thought you meant important dates that you have entered into a list.

The Calendar table is a display. It takes dates and other data from the Important Dates table, where you enter data in the blue cells.

If this is what you are after, please reply with more details of your aim..

Hint: DUR2DAYS is your friend to calculate the Day of Year.

Regards,

Ian.

Samueln1986 wrote:

I have been trying to get dates to highlight a week before there active date in say green and then in red after the active date.

Hi Samuel,

In Numbers 3 you can highlight your cells like this:

Select the cell(s) and in the formatting pane on the right click 'Conditional Highlighting...':

Click the little calendar to work with rules based on dates:

Add two rules that will look something like this:

There are many options in the dropdowns other than those shown in the screenshot.

SG

Samueln1986 wrote:

Thank you very much, this has gotten me much closer to reaching the desired end result.

So I have this set up to give me 60 days out, 90 days out and 180 days out.

This is just for the 60 days out.

Samuel

When you say "60 days out" do you mean the date occurs within 60 days of the current date (today)?

SG

Hi Samuel,

I must confess a little confusion as well.

Here's the top few rows on the table in your post above:

The rules you have set are:

If the date is in the next 30 days, fill the cell with blue.

If the date is in the last 61 days, fill the cell with red.

Those rules are working correctly.

"Next 30 days" and "last 61 days" compare the date in the cell to be formatted with today's date (July 28, 2015 in your example table).

August 31 is 34 days from today, more than 30 days, so no conditional formatting is applied.

August 4 is 7 days from today, which is within 30 days, so its cell is filled blue.

June 9 is 49 days ago, so it is filled red.

March 7 is 143 days ago. That's not in the last 61 days (nor is it in the next 30 days), so it's not filled with either colour.

May 24 is 65 days ago. It would have changed from red filled to white at midnight last Friday.

June 24 is 34 days ago, and will remain red filled for another 27 days, then go back to white filled.

Some clarity as to the purpose of the table and relationship between the dates in columns C, D and E and those in column B might make understanding the table easier. The dates in these columns are after the dates in the same row of column B, but are not 60, 90 and 180 days after July 2, the date in column B.

Regards,

Barry

Samueln1986 wrote:

I don't understand why 7-Mar-15 and 24-May-15 are not red as they are past due according to the 61 days I set it at.

Hi Samuel,

Try increasing the 61 days to something larger, say 365 days or even 5 years. Does that give you the result you want?

SG

OK. Some of the clouds are parting.

Rule #2 is not going to do what you want it to do.

This says "Set the cell fill to red when the date is 1 to 61 days before Today," and that is what the ule is doing on your table.

You want the fill set to red when the date in the cell is more than 60 days before today. Here's what that rule would look like in Numbers v2. The wording or format may be slightly different in Numbers v3:

Note that the date in A1 is for convenience in viewing only (allowing viewers to see what day the image was made). It is not referenced in the formula, which gets the current date directly from the system clock.

An alternate for the rule (which more closely fits your specification) is shown below:

Regards,

Barry

Samueln1986 wrote:

Would be great if apple had a user guide for what can be done with Numbers, even if it would be huge guide would be well worth buying.

There is a "user guide." Have you had a look in your menu at Help > Numbers Help? The section on Conditional Highlighting looks like this:

SG

Sorry to interfere with another important question, How do i highlight the rows at the end of every month (can i use the EOMONTH new function) in the rules?