Another excel poser

  Legolas 16:17 17 Nov 09
Locked

Another excel poser for all the excel geniuses out there.

Is there a way to highlight a cell when a certain date is reached i.e. if the date to be highlighted matches the system date can that cell be automatically highlighted when that date is reached?. So if somewhere on the spreadsheet we have the date 01/12/2009 can the cell containing that date be highlighted when it matches the system date?

Hope I have explained it clearly enough if you need clarification I will expand on the problem.

Thanks

  scotty 16:56 17 Nov 09

What you are describing if Conditional Formatting.

Look in Format menu for Conditional Formatting and follow the instructions. I just tried it by inputting the date and time then setting conditional formatting to change font to red if value was greater than the formula =NOW() and it worked.

  Picklefactory 17:16 17 Nov 09

As cotty says, if you have Excel 2007, then

Highlight the cells you want to format.
Click 'Conditional Formatting'
Select 1st option 'Highlight Cell Rules'
Select 'Less Than'
and in the window that opens type
=TODAY() (Don't miss the = sign)
Choose your formatting option and OK

  Picklefactory 17:17 17 Nov 09

'As Scotty says'

Sorry Scotty :o(

  VoG II 17:19 17 Nov 09

There's a tutorial click here

I would use =TODAY()

  Quickbeam 17:50 17 Nov 09

Does that then mean that when tomorrow comes, tomorrow gets highlighted, and then the next day etc?

  Legolas 19:38 17 Nov 09

I think what most of you are describing would only highlight that days date in that cell, as I will be checking it at the end of each month and identifing those dates highlighted I need all the dates for the month highlighted.

To explain further, I deal with financial records and each case that has closed and the records sent to storage are on an excel spreadsheet 26 tabs A-Z.

Each case has a "case closed date" in a column and in the adjacent column a destruction date so if for instance the case closed on the 17/11/2003 the destruction date would be 17/11/2009 so I want that date highlighted along with any other date within that month that meets the criteria.

Ideally I would like the cell next to the cell with the destruction date in to turn red I can then identify easily those records that have reached their destruction date.

Thanks so far for the suggestions

  VoG II 19:55 17 Nov 09

Say your destruction dates are in column A starting at A1. Select all of the dates, Format > Conditional Formatting, change the drop down to Formula Is and enter the formula

=A1>=TODAY()

then click the Format button, click the Fill tab, choose red fill then OK your way out.

Note: if your dates are in column G starting at G2 then select G2 to the bottom of the data and use the formula

=G2>=TODAY()

  Legolas 20:49 17 Nov 09

Thanks for your suggestion, now if I am doing it right it certainly turns all the selected cells red, the problem is that within column A1 there are dates where the case only closed recently i.e a case may have closed on the 12/08/2008 so the destruction date would be 12/08/2014 If I have followed your method correctly it would also turn that cell red.

  Picklefactory 13:23 18 Nov 09

I'm a little confused (No change there, I hear you say).
Do you want your highlighting to action on the closure date or the destruction date, or both?

If I'm not as confused as usual, then I think you want the cell adjacent to the destruction date (Empty I assume) to turn red to highlight that the destruction date has passed (NOT the closure date).

If that is correct then to follow VoG™'s example of destruction dates being in column A, then:-

Select B1
Select Conditional Formatting > New Rule > Use formula to determine which cells to format.
In teh formula box type '=$A1<TODAY()' and choose your required format, eg fill red and OK
Click and drag that down the column to fill in red all cells adjacent to a date prior to today.

Question? When you have checked out what you must on this record, how do you know to not re-visit the same one over and over, as currently this will remain red. Do you want a means of showing it has been checked?

  Legolas 20:35 18 Nov 09

Thanks for the suggestion. I am home now and cannot really try it out till tomorrow. I did mock up a quick spreadsheet and entered some dates. It did turn the cell red for dates before todays I tried putting my system date forward to try and see if it would turn the preceding days red and I set some to a few years in the future to see if it would leave these unfilled. It worked but only after I redone the formula and formatting and dragged it down. It didn't do it automatically on me changing the system date. Am I doing something wrong or will it work when the system is left to change the date by itself in the normal way?

This thread is now locked and can not be replied to.

Sniper Elite 4 review: Headshotting Nazis has never felt so good

1995-2015: How technology has changed the world in 20 years

The Fresh New Fonts of 2017

WWDC 2017 dates: How to get WWDC 2017 tickets, when is WWDC 2017 and more details announced