Microsoft Excel - Conditional Highlighting Formula (1 Viewer)

[cubd8]

Microsoft Excel - Conditional Highlighting Formula

I want to be able to compare 2 existing date columns and then flag/highlight a celll that shows a difference in dates of 10 days or less. The goal of this exercise is to easily identify projects where the due date is in jeopardy. If there's another method to managing this on dozens of projects where the source data can change and/or new projects be added, I would love advice (and a formula) to easily implement.

Thank You in advance!!!!!!

**

In my spreadsheet, I have 2 columns:

Column E (Order Date) (MM/CC/CCYY format)

Column F (Due Date) (MM/CC/CCYY format)

Is there a formula that will highlight the due date column with a specific color (say red) IF the difference of the order date and due date is 10 days or less (also accounting for January order dates and February due dates)?

3 examples below:

In this example, the 'due date' is 11 days difference from the order date. The due date would not be flagged (or made red).

Order Date = 01/15/2016

Due Date = 01/126/2016

In this example,the order date and due date is less than 10 days apart. I want the 'due date' column to flag the cell red to alert me.

Order Date = 01/28//2016

Due Date = 02/05/2016

In this example,the order date and due date is less than 10 days apart. I want the 'due date' column to flag the cell red to alert me.

Order Date = 01/14//2016

Due Date = 01/23/2016

 

[DallasDMac]

To create the first rule:

1. Select cells A2 through A7. Do this by dragging from A2 to A7.
   
2. Then, click Home > Conditional Formatting > New Rule.
   
3. In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.
   
4. Under Format values where this formula is true, type the formula: =A2>TODAY()
   
   The formula uses the TODAY function to see if the dates in column A are greater than today (in the future). If so, the cells are formatted.
   
5. Click Format.
   
6. In the Color box, select Red. In the Font Style box, select Bold.
   
7. Click OK until the dialog boxes are closed.
   
   The formatting is applied to column A.
   

Needless to say, just edit the formula to say if column f > colmun E + 10

 

[cubd8]

To create the first rule:

1. Select cells A2 through A7. Do this by dragging from A2 to A7.
   
2. Then, click Home > Conditional Formatting > New Rule.
   
3. In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.
   
4. Under Format values where this formula is true, type the formula: =A2>TODAY()
   
   The formula uses the TODAY function to see if the dates in column A are greater than today (in the future). If so, the cells are formatted.
   
5. Click Format.
   
6. In the Color box, select Red. In the Font Style box, select Bold.
   
7. Click OK until the dialog boxes are closed.
   
   The formatting is applied to column A.
   

Needless to say, just edit the formula to say if column f > colmun E + 10

Upon further review, some additional notes:

From a new cell (B5), i want to evaluate 2 existing cells:

Example 1:

Cell E5 (Order Date) = 01/15/2016 and Cell F5 (Due Date) = 02/16/2016

- I want Cell B5 to say "Customer Communication Needed" if F5 / E5 = .50 (meaning the time between the order date and the due date is 1/2 over).

- In this example, B5 would be blank because the difference in dates is .50

Example 2:

Cell E5 (Order Date) = 01/29/2016 and Cell F5 (Due Date) = 02/08/2016

- I want Cell B5 to say "Customer Communication Needed" if F5 / E5 = .50 (meaning the time between the order date and the due date is 1/2 over).

- In this example, B5 would resolve to "Customer Communication Needed" because the difference in dates is GT .50

Sorry for the confusion, I'm trying all sort of ways to make this work.

Any formula help to solve this would be greatly appreciated!

 

[Bottomfeeder Sports]

Upon further review, some additional notes:

From a new cell (B5), i want to evaluate 2 existing cells:

Example 1:

Cell E5 (Order Date) = 01/15/2016 and Cell F5 (Due Date) = 02/16/2016

- I want Cell B5 to say "Customer Communication Needed" if F5 / E5 = .50 (meaning the time between the order date and the due date is 1/2 over).

- In this example, B5 would be blank because the difference in dates is .50

Example 2:

Cell E5 (Order Date) = 01/29/2016 and Cell F5 (Due Date) = 02/08/2016

- I want Cell B5 to say "Customer Communication Needed" if F5 / E5 = .50 (meaning the time between the order date and the due date is 1/2 over).

- In this example, B5 would resolve to "Customer Communication Needed" because the difference in dates is GT .50

Sorry for the confusion, I'm trying all sort of ways to make this work.

Any formula help to solve this would be greatly appreciated!

If I understand the above

=IF((TODAY()- E5) > (F5 - E5)/2, "Customer Communication Needed", "")

 

[cubd8]

Upon further review, some additional notes:

From a new cell (B5), i want to evaluate 2 existing cells:

Example 1:

Cell E5 (Order Date) = 01/15/2016 and Cell F5 (Due Date) = 02/16/2016

- I want Cell B5 to say "Customer Communication Needed" if F5 / E5 = .50 (meaning the time between the order date and the due date is 1/2 over).

- In this example, B5 would be blank because the difference in dates is .50

Example 2:

Cell E5 (Order Date) = 01/29/2016 and Cell F5 (Due Date) = 02/08/2016

- I want Cell B5 to say "Customer Communication Needed" if F5 / E5 = .50 (meaning the time between the order date and the due date is 1/2 over).

- In this example, B5 would resolve to "Customer Communication Needed" because the difference in dates is GT .50

Sorry for the confusion, I'm trying all sort of ways to make this work.

Any formula help to solve this would be greatly appreciated!

If I understand the above

=IF((TODAY()- E5) > (F5 - E5)/2, "Customer Communication Needed", "")

Can you explain how this works with these 2 examples? How does the "customer communication needed" value get displayed?

Cell E5 (Order Date) = 01/15/2016 and Cell F5 (Due Date) = 02/16/2016

With today being 02/04:

02/04 - 01/15 > 02/16 - 01/15 ) / 2 resolves to (20 days > 32 days) / 2 = 0.625

Am I interpreting this correctly?

Thanks for the help!

 

[Donkey Derp]

To create the first rule:

1. Select cells A2 through A7. Do this by dragging from A2 to A7.
   
2. Then, click Home > Conditional Formatting > New Rule.
   
3. In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.
   
4. Under Format values where this formula is true, type the formula: =A2>TODAY()
   
   The formula uses the TODAY function to see if the dates in column A are greater than today (in the future). If so, the cells are formatted.
   
5. Click Format.
   
6. In the Color box, select Red. In the Font Style box, select Bold.
   
7. Click OK until the dialog boxes are closed.
   
   The formatting is applied to column A.
   

Needless to say, just edit the formula to say if column f > colmun E + 10

Upon further review, some additional notes:

From a new cell (B5), i want to evaluate 2 existing cells:

Example 1:

Cell E5 (Order Date) = 01/15/2016 and Cell F5 (Due Date) = 02/16/2016

- I want Cell B5 to say "Customer Communication Needed" if F5 / E5 = .50 (meaning the time between the order date and the due date is 1/2 over).

- In this example, B5 would be blank because the difference in dates is .50

Example 2:

Cell E5 (Order Date) = 01/29/2016 and Cell F5 (Due Date) = 02/08/2016

- I want Cell B5 to say "Customer Communication Needed" if F5 / E5 = .50 (meaning the time between the order date and the due date is 1/2 over).

- In this example, B5 would resolve to "Customer Communication Needed" because the difference in dates is GT .50

Sorry for the confusion, I'm trying all sort of ways to make this work.

Any formula help to solve this would be greatly appreciated!

What do you mean the difference in dates is .50?

 

[Donkey Derp]

Microsoft Excel - Conditional Highlighting Formula

I want to be able to compare 2 existing date columns and then flag/highlight a celll that shows a difference in dates of 10 days or less. The goal of this exercise is to easily identify projects where the due date is in jeopardy. If there's another method to managing this on dozens of projects where the source data can change and/or new projects be added, I would love advice (and a formula) to easily implement.

Thank You in advance!!!!!!

**

In my spreadsheet, I have 2 columns:

Column E (Order Date) (MM/CC/CCYY format)

Column F (Due Date) (MM/CC/CCYY format)

Is there a formula that will highlight the due date column with a specific color (say red) IF the difference of the order date and due date is 10 days or less (also accounting for January order dates and February due dates)?

3 examples below:

In this example, the 'due date' is 11 days difference from the order date. The due date would not be flagged (or made red).

Order Date = 01/15/2016

Due Date = 01/126/2016

In this example,the order date and due date is less than 10 days apart. I want the 'due date' column to flag the cell red to alert me.

Order Date = 01/28//2016

Due Date = 02/05/2016

In this example,the order date and due date is less than 10 days apart. I want the 'due date' column to flag the cell red to alert me.

Order Date = 01/14//2016

Due Date = 01/23/2016

In example 1, if 1/15/16 is in A1 and 1/26/16 is in B1

Click on A1

Click on Home in your taskbar

Click on Conditional Formatting in your taskbar

Click on New Rule...

Click on "Use a formula to determine which cells to format"

Under "Format values where this formula is true" manually type: =IF(B1-A1<10,"TRUE","FALSE")

Next to "Preview", click "Format...". Click "Fill" and select RED. Click "Ok". Click "Ok" again.

Copy Cell A1

Highlight the rest of the cells in column A that you want to apply this conditional formatting to and paste formatting (Under "Paste" then click "Formatting")

 

[Donkey Derp]

Upon further review, some additional notes:

From a new cell (B5), i want to evaluate 2 existing cells:

Example 1:

Cell E5 (Order Date) = 01/15/2016 and Cell F5 (Due Date) = 02/16/2016

- I want Cell B5 to say "Customer Communication Needed" if F5 / E5 = .50 (meaning the time between the order date and the due date is 1/2 over).

- In this example, B5 would be blank because the difference in dates is .50

Example 2:

Cell E5 (Order Date) = 01/29/2016 and Cell F5 (Due Date) = 02/08/2016

- I want Cell B5 to say "Customer Communication Needed" if F5 / E5 = .50 (meaning the time between the order date and the due date is 1/2 over).

- In this example, B5 would resolve to "Customer Communication Needed" because the difference in dates is GT .50

Sorry for the confusion, I'm trying all sort of ways to make this work.

Any formula help to solve this would be greatly appreciated!

If I understand the above

=IF((TODAY()- E5) > (F5 - E5)/2, "Customer Communication Needed", "")

Can you explain how this works with these 2 examples? How does the "customer communication needed" value get displayed?

Cell E5 (Order Date) = 01/15/2016 and Cell F5 (Due Date) = 02/16/2016

With today being 02/04:

02/04 - 01/15 > 02/16 - 01/15 ) / 2 resolves to (20 days > 32 days) / 2 = 0.625

Am I interpreting this correctly?

Thanks for the help!

An "IF" statement works as this =IF(the first section before the comma is the question you are asking Excel, then do this after the comma if the question is true, if it is not true do what is after the second comma)

=IF((TODAY()- E5) > (F5 - E5)/2, "Customer Communication Needed", "")

This is the question you are asking: (TODAY()- E5) > (F5 - E5)/2

If it is true, Excel will display: "Customer Communication Needed"

If it is false, Excel will display nothing (which is denoted "" in his formula)

try a simple example

type 1 in cell A1

type 2 in cell A2

type =IF(A1<A2,"A1 is less than A2","A2 is less than A1") in cell A3

now change A1 and A2 to 5,3; 10,4; 2,7; etc. and see A3 change.

 

[Bottomfeeder Sports]

Upon further review, some additional notes:

From a new cell (B5), i want to evaluate 2 existing cells:

Example 1:

Cell E5 (Order Date) = 01/15/2016 and Cell F5 (Due Date) = 02/16/2016

- I want Cell B5 to say "Customer Communication Needed" if F5 / E5 = .50 (meaning the time between the order date and the due date is 1/2 over).

- In this example, B5 would be blank because the difference in dates is .50

Example 2:

Cell E5 (Order Date) = 01/29/2016 and Cell F5 (Due Date) = 02/08/2016

- I want Cell B5 to say "Customer Communication Needed" if F5 / E5 = .50 (meaning the time between the order date and the due date is 1/2 over).

- In this example, B5 would resolve to "Customer Communication Needed" because the difference in dates is GT .50

Sorry for the confusion, I'm trying all sort of ways to make this work.

Any formula help to solve this would be greatly appreciated!

If I understand the above

=IF((TODAY()- E5) > (F5 - E5)/2, "Customer Communication Needed", "")

Can you explain how this works with these 2 examples? How does the "customer communication needed" value get displayed?

Cell E5 (Order Date) = 01/15/2016 and Cell F5 (Due Date) = 02/16/2016

With today being 02/04:

02/04 - 01/15 > 02/16 - 01/15 ) / 2 resolves to (20 days > 32 days) / 2 = 0.625

Am I interpreting this correctly?

Thanks for the help!

Using your numbers from above...=IF((TODAY()- E5) > (F5 - E5)/2 , "Customer Communication Needed", "")

=IF(((02/04- 01/15)) > (02/16- 01/15)/2 , "Customer Communication Needed", "")

=IF(((20)) > (32)/2 , "Customer Communication Needed", "")

=IF((20) > 16 , "Customer Communication Needed", "")

=IF(20 > 16 , "Customer Communication Needed", "")

=IF( TRUE , ("Customer Communication Needed"), "")

Just changed E5 to 01/29, left F5 at 2/16:

=IF((TODAY()- E5) > (F5 - E5)/2 , "Customer Communication Needed", "")

=IF(((02/04- 01/29)) > (02/16- 01/29)/2 , "Customer Communication Needed", "")

=IF(((6)) > (18)/2 , "Customer Communication Needed", "")

=IF((6) > 9 , "Customer Communication Needed", "")

=IF(6 > 9 , "Customer Communication Needed", "")

=IF( FALSE , "Customer Communication Needed", (""))

Hopefully I don't have any simple arithmetic errors....

 

[slackjawedyokel]

.