Page 1 of 1
[Solved] IF Function used to highlight a date that is passed
Posted: Mon Oct 21, 2013 10:13 pm
by TRICKYDICKY
Help
This is my 1st time on a forum can someone help me I would like to be able to format a cell so that if the date in it is in the past it turns to red ideally it would pick up the current date is this possible? now for the difficult part if it can be done it needs to be explained so that a 55yr old computer shy luddite can understand.
Re: IF Function used to highlight a date that is passed .
Posted: Tue Oct 22, 2013 12:45 am
by acknak
Greetings and welcome to the community forum!
1) Click on the cell with the date you want to format.
2) Make sure that the value in the cell is a numeric date value--use View > Value Highlighting: ON. Does the date value appear as blue (or maybe green) text? That means it's a numeric value; good! If it appears as black characters, then it's a text value and you have to fix that before you can go on.
Value Highlighting: OFF
3) Format > Conditional Formatting
Choose the settings and enter the conditional value as:
Cell value is: less than TODAY()
Click on "New Style"
In the new style window, give it a name, something like "Past Date" maybe, then click on the Background tab and choose a color. Click OK.
Click OK on the conditional formatting dialog window.
Re: IF Function used to highlight a date that is passed .
Posted: Tue Oct 22, 2013 11:12 am
by TRICKYDICKY
Many thanks it worked ! I'm grateful for the clear concise answer .
Of course there is a downside which is I have another question is it possible to by clicking on a cell open up a separate sheet in the workbook.
Re: IF Function used to highlight a date that is passed .
Posted: Tue Oct 22, 2013 1:18 pm
by acknak
TRICKYDICKY wrote:... is it possible to by clicking on a cell open up a separate sheet in the workbook.
For future questions, it's better all around to start a new topic for each question rather than adding a different question to the same topic--but no worries
Insert > Hyperlink
At left, choose link type: Document
Click on the target icon at the right of the "Target" slot, then select the sheet you want to link to. Click "Apply" and "Close" on the target window.
Enter the link text (Text: ...) and then "Apply" and "Close" on the hyperlink window.
This gives you a link to the other sheet; clicking on the link will jump to the linked sheet.
Re: IF Function used to highlight a date that is passed .
Posted: Tue Oct 22, 2013 4:52 pm
by TRICKYDICKY
Thanks again for your help. That should keep me on the right track for a while. Oh and took it on board about new topic new question " it's logical" ( I'm sure someone famous uses that phrase).