Highlighting a cell when a date comes

[Engineeeeeeeeer]

I have an Excel sheet I use to manage numerous projects. I have the project tasks in the first column, and then the different projects along the top row. When a new project comes along I add the project name in the next column in the top row & I input dates i need to do the tasks by by writing ' by 5/7/05 ' for example.

I have to review the sheet daily to see if any deadlines are looming. My question is - Is there a way that when the date occurs (ie it is the 5/7/05), the cell background colour automatically changes to red, thus highlighting that particular task is due. The solution must include the 'by' word, to indicate the task should be done by then

Help on this would be very much appreciated. - Thanks

 

[KatiLynSki]

Okay well I started to figure out a solution but I have hit a deadend so maybe this will help someone else get you a solution...

B15 contains "by 6/29/2005"

=IF(ISERROR(FIND("by",B15)),"not by",IF(ISERROR(FIND("6/29/2005",B15)),"not today","TODAY")) ... this equation will put TODAY if its today

i think you can replace "6/29/2005" with text(today())... so that the equation is

=IF(ISERROR(FIND("by",B15)),"not by",IF(ISERROR(FIND(text(today()),B15)),"not today","TODAY")) ... but that isn't working for some reason

You should be able to put these equations into conditional formating to change the cell color...

I will be interested to see what other people come up with.

 

[johnwm]

Instead of typing in 'by 28/6/2005' just type in the actual dates. Then select all the date-containing cells and go to Format|Cells and select the number tab. Select Custom from the dropdown and type into the box '"By "dd/mm/yy' (without the single quotes). The dates will now display correctly, but still contain actual dates (only the display has changed).

Now you can use conditional formatting to get the Red background. In an unused cell (for this example A28) type in '=TODAY()' (without the single quotes). Select all the date cells then go to Format|Conditional Formatting. Set Condition 1 as Cell Value is equal to =$A$28. Click the adjacent Format button, select the Patterns tab and choose your colour.

That's it - any cell in the chosen range with today's date will have your Red background!

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk

 

[jhardy1]

In addition to johnwm's suggestion, you can use multiple conditional formats to differentiate between "overdue" and "now due" activities. For example, if you have today's date (using "=TODAY()") in cell A28:

Condition 1: If Cell Value < $A$28 (i.e. task is now overdue) you might use bold font in a red cell.

Condition 2: If Cell Value = $A$28 (i.e. task is due today), you might use another colour code (maybe a green cell).

For all other cases (not yet due), just use default format (no conditional format needs to be set). Anything which has default formatting is therefore not yet due.

 

[MintJulep]

something along the lines of

=DATEVALUE(RIGHT(TRIM(B15),LEN(TRIM(B15))-3))

should return only the date portion of the "by 6/16/06" date. Then you could use date functions as needed in conditional formatting.

Life would be much easier if your column heading could be "due by", and you dispense with the "by" in each cell.

Or better yet, use an actual scheduling program like MS Project or Primavera.

 

[johnwm]

MintJulep
You may find it's a lot easier to store the actual date and use the Custom Format to display the 'By ' as shown in my post above!

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk

 

[Engineeeeeeeeer]

Thanks very much everyone. I've decided to go with the Johnwm solution - TQ.

However, this has highlighted another issue. Once the task is complete, I usually type 'Yes 30/6/05', for example if the task was completed today. With the conditional formating, this cell is still the conditional formated colour.
I'm thinking... Could there be a 'hotkey' (for eg ctrl-T) that holds a macro (?) that would clear the conditional formatting and enter Yes and todays date automatically.

Any Ideas????

 

[Dinosaur]

While not directly related to your question; I used a spreadsheet to manage my work in a similar manner at an earlier job. The first column contains the project name or number. The second column contains the specific date for an activity. The third column contains the specific task that need to be completed. With all this info, you can then sort the entire sheet based on the date in the second column and you don't need to do any major searching in the future. All your deadlines come up in chronological order.

Good Luck. - Ed

 

[pmover]

not sure why you opt for the "by", but excel does have conditional format capabilities based on value or formula within the cell.

for conditional format, select Format-Conditional Formatting and proceed from there.

furthermore, there are many websites that offer tips for using conditional formats.

good luck!

-pmover

 

[francesca]

You could change the formatting with a macro on the sheet that runs every time you activate the sheet. (I tried on every selection change, but it got really annoying!)

This will also probably keep your file size down by reducing the complexity of the formulae in your cells.

This code should work if you copy and paste it into your VB Editor and just change the starting cell to the first date field you have. Of course, it'll fall over if you have any blank date fields midway down your column, but you can change the loop criteria to the limits of your date field.

You can also obviously change the formatting of the fields by changing the color numbers in the format code.

[COLOR=blue]Private Sub [/color]Worksheet_Activate()

[COLOR=blue]Dim [/color]intDate# [COLOR=green]'declare variable intDate as integer[/color]
[COLOR=blue]Dim [/color]intToday# [COLOR=green]'declare variable intToday to hold today's date[/color]

intToday = Date [COLOR=green]'set today's date
'Date is the function for the system date on your computer

'move to the first cell in your date column[/color]
Range("P1").Select
[COLOR=green]'initialize intDate[/color]
intDate = Range("P1").Value

[COLOR=green]'start a loop to go down your spreadsheet date field.
'the date field is an integer so we want > 0
'not = "" to denote empty cell[/color]
[COLOR=blue]Do

    If [/color]intDate > intToday [COLOR=blue]Then [/color][COLOR=green]'task is due in the future
        'set format to black[/color]
         Selection.NumberFormat = "m/d/yyyy;@"
[COLOR=blue]             With [/color]Selection.Font
                 .ColorIndex = xlAutomatic
[COLOR=blue]            End With[/color]
         Selection.Interior.ColorIndex = xlNone

[COLOR=blue]    ElseIf [/color]intDate = intToday [COLOR=blue]Then [/color] [COLOR=green]'task is due today
         'set format to red fill and white text[/color]
         Selection.NumberFormat = "m/d/yyyy;@"
[COLOR=blue]             With [/color]Selection.Font
                .ColorIndex = 2
[COLOR=blue]             End With
             With[/color] Selection.Interior
                 .ColorIndex = 3
                 .Pattern = xlSolid
[COLOR=blue]             End With
            
    ElseIf [/color]intDate < intToday [COLOR=blue]Then [/color][COLOR=green]'task is overdue
         'set format to red text on yellow fill[/color]
         Selection.NumberFormat = "m/d/yyyy;@"
[COLOR=blue]             With [/color]Selection.Font
                .ColorIndex = 3
[COLOR=blue]             End With
             With [/color]Selection.Interior
                 .ColorIndex = 6
                 .Pattern = xlSolid
[COLOR=blue]             End With

    Else [/color][COLOR=green]'there is no other option, but oh well[/color]
[COLOR=blue]         Exit Do [/color][COLOR=green]'get out of the loop, something's wrong!![/color]

[COLOR=blue]    End If[/color][COLOR=green] 'end your if statement![/color]

    ActiveCell.Offset(1, 0).Select [COLOR=green]'go to next cell in column[/color]
    intDate = ActiveCell.Value  [COLOR=green]' set intDate = date in the new cell[/color]

[COLOR=blue]Loop Until [/color]intDate = 0 [COLOR=green]'terminate your loop statement

'remove your variables from memory[/color]
intDate = [COLOR=blue]Empty[/color]
intToday = [COLOR=blue]Empty

End Sub[/color]

Good luck!