Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to highlight cells if it passes a set date?
Hi all
I have 100+ clients on excel, all have certain certificates with different expiry dates. What I would like is either of the two. 1: The to highlight in red say if the expiry date is within 30-60 days of todays date. 2: Have a seperate worksheet available whereby it will update automatically with the name and certification that will expiry and of course when. Obviously the reason for this is to ensure I remain compliant and chase those candidates with soon to expire qualifications. Thanks in advance :) G |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to highlight cells if it passes a set date?
To answer the date question you could use a conditional format on the cell
containing the date (A1 in this example). Set the condition to Formula Is and then enter =$A$1-30<TODAY() and then set the formatting to whatever you require i.e. Bold and Red. This will then highlight the date when it comes within 30 days of today's date. Hope that helps. "Gareth" wrote: Hi all I have 100+ clients on excel, all have certain certificates with different expiry dates. What I would like is either of the two. 1: The to highlight in red say if the expiry date is within 30-60 days of todays date. 2: Have a seperate worksheet available whereby it will update automatically with the name and certification that will expiry and of course when. Obviously the reason for this is to ensure I remain compliant and chase those candidates with soon to expire qualifications. Thanks in advance :) G |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to highlight cells if it passes a set date?
Hi Pyrite
thanks for your quick response. I cant seem to find conditioning format. I have added conditional formatting to the ribbon, however when selected, this only gives options on things like highlight cells top/bottom rules,, data bars etc. nor is there anywhere that says Forumla Is. any clues? G "Pyrite" wrote: To answer the date question you could use a conditional format on the cell containing the date (A1 in this example). Set the condition to Formula Is and then enter =$A$1-30<TODAY() and then set the formatting to whatever you require i.e. Bold and Red. This will then highlight the date when it comes within 30 days of today's date. Hope that helps. "Gareth" wrote: Hi all I have 100+ clients on excel, all have certain certificates with different expiry dates. What I would like is either of the two. 1: The to highlight in red say if the expiry date is within 30-60 days of todays date. 2: Have a seperate worksheet available whereby it will update automatically with the name and certification that will expiry and of course when. Obviously the reason for this is to ensure I remain compliant and chase those candidates with soon to expire qualifications. Thanks in advance :) G |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to highlight cells if it passes a set date?
Highlight all the 'expiry date' cells (hold Ctrl whilst left click each if
they are split up) and then select 'Format' from the menu and then Conditional Formatting. On the left of the dialogue box there is options to select if the cell value is greater than etc, use the drop down that currently says Cell Value Is and change this to Formula Is then input the formula, it will then apply to those cells. The thing to be careful of is that you need each date cell to refer to itself otherwise it may change when the first one is within 30 days etc. That is for Excel 2003. Let me know if you are not using that. Otherwise I hope that makes more sense and you can now implement it. "Gareth" wrote: Hi Pyrite thanks for your quick response. I cant seem to find conditioning format. I have added conditional formatting to the ribbon, however when selected, this only gives options on things like highlight cells top/bottom rules,, data bars etc. nor is there anywhere that says Forumla Is. any clues? G "Pyrite" wrote: To answer the date question you could use a conditional format on the cell containing the date (A1 in this example). Set the condition to Formula Is and then enter =$A$1-30<TODAY() and then set the formatting to whatever you require i.e. Bold and Red. This will then highlight the date when it comes within 30 days of today's date. Hope that helps. "Gareth" wrote: Hi all I have 100+ clients on excel, all have certain certificates with different expiry dates. What I would like is either of the two. 1: The to highlight in red say if the expiry date is within 30-60 days of todays date. 2: Have a seperate worksheet available whereby it will update automatically with the name and certification that will expiry and of course when. Obviously the reason for this is to ensure I remain compliant and chase those candidates with soon to expire qualifications. Thanks in advance :) G |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to highlight cells if it passes a set date?
To elaborate a little more, in cell A1 you need to make sure that the formula
in the conditional formatting reads A1 and then in A2 it must read A2 etc. If you dont have dates in the range already you could format the first cell to conditonal format and then simply copy down the row. If you intend to do this change the formula slightly by removing the $ symbols, like this =A1-30<TODAY() When you copy this down it should change the cell reference automatically. To check this just select a few individual cells at random and open the conditional formatting dialogue box via the Format menu and make sure they refer to themselves. You can also test by putting dates in that should react and ones that shouldnt. "Gareth" wrote: Hi Pyrite thanks for your quick response. I cant seem to find conditioning format. I have added conditional formatting to the ribbon, however when selected, this only gives options on things like highlight cells top/bottom rules,, data bars etc. nor is there anywhere that says Forumla Is. any clues? G "Pyrite" wrote: To answer the date question you could use a conditional format on the cell containing the date (A1 in this example). Set the condition to Formula Is and then enter =$A$1-30<TODAY() and then set the formatting to whatever you require i.e. Bold and Red. This will then highlight the date when it comes within 30 days of today's date. Hope that helps. "Gareth" wrote: Hi all I have 100+ clients on excel, all have certain certificates with different expiry dates. What I would like is either of the two. 1: The to highlight in red say if the expiry date is within 30-60 days of todays date. 2: Have a seperate worksheet available whereby it will update automatically with the name and certification that will expiry and of course when. Obviously the reason for this is to ensure I remain compliant and chase those candidates with soon to expire qualifications. Thanks in advance :) G |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to highlight cells if it passes a set date?
Hi there Pyrite, thanks for your messages, trying to get my head around it.
I am using Excel 2007. I am trying to put in the following formula =$E$2:$E$34-30<TODAY() I am using colum E to put all the dates in. Obvioulsy what i want is excel to highlight the cell which is within 30 days (or whatever number of days) of todays date. the above formula doesnt seem to do anything. any clues? Alternativly, is there a way to enter the formula in yourself and email me a copy so i can see what you have done Thanks "Pyrite" wrote: To elaborate a little more, in cell A1 you need to make sure that the formula in the conditional formatting reads A1 and then in A2 it must read A2 etc. If you dont have dates in the range already you could format the first cell to conditonal format and then simply copy down the row. If you intend to do this change the formula slightly by removing the $ symbols, like this =A1-30<TODAY() When you copy this down it should change the cell reference automatically. To check this just select a few individual cells at random and open the conditional formatting dialogue box via the Format menu and make sure they refer to themselves. You can also test by putting dates in that should react and ones that shouldnt. "Gareth" wrote: Hi Pyrite thanks for your quick response. I cant seem to find conditioning format. I have added conditional formatting to the ribbon, however when selected, this only gives options on things like highlight cells top/bottom rules,, data bars etc. nor is there anywhere that says Forumla Is. any clues? G "Pyrite" wrote: To answer the date question you could use a conditional format on the cell containing the date (A1 in this example). Set the condition to Formula Is and then enter =$A$1-30<TODAY() and then set the formatting to whatever you require i.e. Bold and Red. This will then highlight the date when it comes within 30 days of today's date. Hope that helps. "Gareth" wrote: Hi all I have 100+ clients on excel, all have certain certificates with different expiry dates. What I would like is either of the two. 1: The to highlight in red say if the expiry date is within 30-60 days of todays date. 2: Have a seperate worksheet available whereby it will update automatically with the name and certification that will expiry and of course when. Obviously the reason for this is to ensure I remain compliant and chase those candidates with soon to expire qualifications. Thanks in advance :) G |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to highlight cells if it passes a set date?
As a further note, regarding the formula i have just written, if I changed
the first cell then all other cells turn green. So formula does work in a way but obvioulsy i need individual cells to be highlighted. "Pyrite" wrote: To elaborate a little more, in cell A1 you need to make sure that the formula in the conditional formatting reads A1 and then in A2 it must read A2 etc. If you dont have dates in the range already you could format the first cell to conditonal format and then simply copy down the row. If you intend to do this change the formula slightly by removing the $ symbols, like this =A1-30<TODAY() When you copy this down it should change the cell reference automatically. To check this just select a few individual cells at random and open the conditional formatting dialogue box via the Format menu and make sure they refer to themselves. You can also test by putting dates in that should react and ones that shouldnt. "Gareth" wrote: Hi Pyrite thanks for your quick response. I cant seem to find conditioning format. I have added conditional formatting to the ribbon, however when selected, this only gives options on things like highlight cells top/bottom rules,, data bars etc. nor is there anywhere that says Forumla Is. any clues? G "Pyrite" wrote: To answer the date question you could use a conditional format on the cell containing the date (A1 in this example). Set the condition to Formula Is and then enter =$A$1-30<TODAY() and then set the formatting to whatever you require i.e. Bold and Red. This will then highlight the date when it comes within 30 days of today's date. Hope that helps. "Gareth" wrote: Hi all I have 100+ clients on excel, all have certain certificates with different expiry dates. What I would like is either of the two. 1: The to highlight in red say if the expiry date is within 30-60 days of todays date. 2: Have a seperate worksheet available whereby it will update automatically with the name and certification that will expiry and of course when. Obviously the reason for this is to ensure I remain compliant and chase those candidates with soon to expire qualifications. Thanks in advance :) G |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to highlight cells if it passes a set date?
The CF formula you want in row 2 is =$E2-30<TODAY()
Either select the whole range (with the active cell being in row 2) and then insert the above as your CF formula and it will update the formula for susequent rows. Or Put the above condition in row 2 and use Format Painter to copy down Or Put the above condition in row 2, copy that cell, and use Edit/ Paste special/ Format to paste the format down your range. -- David Biddulph "Gareth" wrote in message ... Hi there Pyrite, thanks for your messages, trying to get my head around it. I am using Excel 2007. I am trying to put in the following formula =$E$2:$E$34-30<TODAY() I am using colum E to put all the dates in. Obvioulsy what i want is excel to highlight the cell which is within 30 days (or whatever number of days) of todays date. the above formula doesnt seem to do anything. any clues? Alternativly, is there a way to enter the formula in yourself and email me a copy so i can see what you have done Thanks "Pyrite" wrote: To elaborate a little more, in cell A1 you need to make sure that the formula in the conditional formatting reads A1 and then in A2 it must read A2 etc. If you dont have dates in the range already you could format the first cell to conditonal format and then simply copy down the row. If you intend to do this change the formula slightly by removing the $ symbols, like this =A1-30<TODAY() When you copy this down it should change the cell reference automatically. To check this just select a few individual cells at random and open the conditional formatting dialogue box via the Format menu and make sure they refer to themselves. You can also test by putting dates in that should react and ones that shouldnt. "Gareth" wrote: Hi Pyrite thanks for your quick response. I cant seem to find conditioning format. I have added conditional formatting to the ribbon, however when selected, this only gives options on things like highlight cells top/bottom rules,, data bars etc. nor is there anywhere that says Forumla Is. any clues? G "Pyrite" wrote: To answer the date question you could use a conditional format on the cell containing the date (A1 in this example). Set the condition to Formula Is and then enter =$A$1-30<TODAY() and then set the formatting to whatever you require i.e. Bold and Red. This will then highlight the date when it comes within 30 days of today's date. Hope that helps. "Gareth" wrote: Hi all I have 100+ clients on excel, all have certain certificates with different expiry dates. What I would like is either of the two. 1: The to highlight in red say if the expiry date is within 30-60 days of todays date. 2: Have a seperate worksheet available whereby it will update automatically with the name and certification that will expiry and of course when. Obviously the reason for this is to ensure I remain compliant and chase those candidates with soon to expire qualifications. Thanks in advance :) G |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making a date go red, if date passes todays date. | Excel Worksheet Functions | |||
Deleting a row when closing date passes. | Excel Discussion (Misc queries) | |||
Highlight cells with ctrl-click but only un-highlight one cell | Excel Discussion (Misc queries) | |||
how can I set an alarm for when a date passes? | Excel Discussion (Misc queries) | |||
when mouse passes over button, no description is given | Excel Discussion (Misc queries) |