Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Can I get Excel to automatically BOLD dates 30d before expiration?
I am new to Excel (obviously), and I have created a simple worksheet that
contains numerous dates. I wish to have the dates become bold when they are to expire within 30 days. Is there a formula for that purpose? Thanks... |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Can I get Excel to automatically BOLD dates 30d before expiration?
Select the range of cells to be formatted
Use the command Format | Conditional Formatting In the dialog, specify: Formula Is : =AND(A1TODAY(),A1-TODAY()<30) and click the Format button the dialog to make bold and/or colour the cells) NOTE: in this formula change A1 to the first (the one that is uppermost to the left) cell in your range best wishes and Happy New Year -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Donna" wrote in message ... I am new to Excel (obviously), and I have created a simple worksheet that contains numerous dates. I wish to have the dates become bold when they are to expire within 30 days. Is there a formula for that purpose? Thanks... |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Can I get Excel to automatically BOLD dates 30d before expiration?
Try conditional formatting
(Steps in xl2003) Assuming dates running in A1 down (real dates are presumed) Select col A (A1 active) Click Format Conditional Formatting Under Condition 1, Formula Is: =AND(A1<"",A1-TODAY()<=30) Format Font tab Bold OK OK out -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Donna" wrote: I am new to Excel (obviously), and I have created a simple worksheet that contains numerous dates. I wish to have the dates become bold when they are to expire within 30 days. Is there a formula for that purpose? Thanks... |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Can I get Excel to automatically BOLD dates 30d before expiration?
Donna
FormatConditional FormattingFormula is: =A1=TODAY()-30 Format to your color of choice from Patterns Tab Gord Dibben MS Excel MVP On Thu, 27 Dec 2007 11:57:03 -0800, Donna wrote: I am new to Excel (obviously), and I have created a simple worksheet that contains numerous dates. I wish to have the dates become bold when they are to expire within 30 days. Is there a formula for that purpose? Thanks... |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Can I get Excel to automatically BOLD dates 30d before expirat
Bernard...thank you very much! Happy New Year to you too!!
Donna "Bernard Liengme" wrote: Select the range of cells to be formatted Use the command Format | Conditional Formatting In the dialog, specify: Formula Is : =AND(A1TODAY(),A1-TODAY()<30) and click the Format button the dialog to make bold and/or colour the cells) NOTE: in this formula change A1 to the first (the one that is uppermost to the left) cell in your range best wishes and Happy New Year -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Donna" wrote in message ... I am new to Excel (obviously), and I have created a simple worksheet that contains numerous dates. I wish to have the dates become bold when they are to expire within 30 days. Is there a formula for that purpose? Thanks... |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Can I get Excel to automatically BOLD dates 30d before expirat
Bernard, can you help me with one other please?
What would the formula be for changing formula for greater than 30 days but less than 60?? In other words when the expiration date is less than 60 days away, but greater than 30 days away. I really appreciate your time!! Donna "Bernard Liengme" wrote: Select the range of cells to be formatted Use the command Format | Conditional Formatting In the dialog, specify: Formula Is : =AND(A1TODAY(),A1-TODAY()<30) and click the Format button the dialog to make bold and/or colour the cells) NOTE: in this formula change A1 to the first (the one that is uppermost to the left) cell in your range best wishes and Happy New Year -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Donna" wrote in message ... I am new to Excel (obviously), and I have created a simple worksheet that contains numerous dates. I wish to have the dates become bold when they are to expire within 30 days. Is there a formula for that purpose? Thanks... |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Can I get Excel to automatically BOLD dates 30d before expirat
.. when the expiration date is less than 60 days away, but
greater than 30 days away. Think you could put it in CF's Condition 2 as: =AND(A1-TODAY()30,A1-TODAY()<60) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Can I get Excel to automatically BOLD dates 30d before expirat
This isn't working for me. What could I be doing wrong?
"Bernard Liengme" wrote: Select the range of cells to be formatted Use the command Format | Conditional Formatting In the dialog, specify: Formula Is : =AND(A1TODAY(),A1-TODAY()<30) and click the Format button the dialog to make bold and/or colour the cells) NOTE: in this formula change A1 to the first (the one that is uppermost to the left) cell in your range best wishes and Happy New Year -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Donna" wrote in message ... I am new to Excel (obviously), and I have created a simple worksheet that contains numerous dates. I wish to have the dates become bold when they are to expire within 30 days. Is there a formula for that purpose? Thanks... |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Can I get Excel to automatically BOLD dates 30d before expirat
Firstly, go back into Format/ Condition Formatting/ Formula Is, and check
that the formula is what you intended it to be. Excel has a habit of changing things, particularly if you forgot to enter the = sign at the start of the formula. Check also that it is referring to the correct cell. Check again that the Format option within your Conditional Formatting condition is set to what you thought you'd set (bold, or whatever). If the formula looks as you expected, are you sure that A1 contains a real Excel date (and not a text string that looks like a date)? If you temporaily reformat the cell as General, you should see a number between 39457 and 39485 if it is to satisfy your test. -- David Biddulph "Garza" wrote in message ... This isn't working for me. What could I be doing wrong? "Bernard Liengme" wrote: Select the range of cells to be formatted Use the command Format | Conditional Formatting In the dialog, specify: Formula Is : =AND(A1TODAY(),A1-TODAY()<30) and click the Format button the dialog to make bold and/or colour the cells) NOTE: in this formula change A1 to the first (the one that is uppermost to the left) cell in your range best wishes and Happy New Year -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Donna" wrote in message ... I am new to Excel (obviously), and I have created a simple worksheet that contains numerous dates. I wish to have the dates become bold when they are to expire within 30 days. Is there a formula for that purpose? Thanks... |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Can I get Excel to automatically BOLD dates 30d before expirat
It is still not taking it... So if it's column B to E what should I change
the cell to say.. Sorry I really do appreciate your help on this. "David Biddulph" wrote: Firstly, go back into Format/ Condition Formatting/ Formula Is, and check that the formula is what you intended it to be. Excel has a habit of changing things, particularly if you forgot to enter the = sign at the start of the formula. Check also that it is referring to the correct cell. Check again that the Format option within your Conditional Formatting condition is set to what you thought you'd set (bold, or whatever). If the formula looks as you expected, are you sure that A1 contains a real Excel date (and not a text string that looks like a date)? If you temporaily reformat the cell as General, you should see a number between 39457 and 39485 if it is to satisfy your test. -- David Biddulph "Garza" wrote in message ... This isn't working for me. What could I be doing wrong? "Bernard Liengme" wrote: Select the range of cells to be formatted Use the command Format | Conditional Formatting In the dialog, specify: Formula Is : =AND(A1TODAY(),A1-TODAY()<30) and click the Format button the dialog to make bold and/or colour the cells) NOTE: in this formula change A1 to the first (the one that is uppermost to the left) cell in your range best wishes and Happy New Year -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Donna" wrote in message ... I am new to Excel (obviously), and I have created a simple worksheet that contains numerous dates. I wish to have the dates become bold when they are to expire within 30 days. Is there a formula for that purpose? Thanks... |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Can I get Excel to automatically BOLD dates 30d before expirat
One more time............no formula is entered "in a cell" so there is no cell
to change. With "real" dates in A1 to A100 starting with January 1, 2008 Make sure they are real dates and not text. Select B1:E100 FormatConditional FormattingFormula is: =AND(A1TODAY(),A1-TODAY()<30) entered in the dialog box. Format to a nice color and OK your way out. A1:A30 should be colored. If not, check the formula in CF to be sure it is exactly as written. As David points out, Excel will sometimes change things. When I pasted =AND(A1TODAY(),A1-TODAY()<30) in the Formula is dialog, Excel altered it to ="=AND(A1TODAY(),A1-TODAY()<30)" which would not work. Gord Dibben MS Excel MVP On Wed, 9 Jan 2008 14:46:25 -0800, Garza wrote: It is still not taking it... So if it's column B to E what should I change the cell to say.. Sorry I really do appreciate your help on this. "David Biddulph" wrote: Firstly, go back into Format/ Condition Formatting/ Formula Is, and check that the formula is what you intended it to be. Excel has a habit of changing things, particularly if you forgot to enter the = sign at the start of the formula. Check also that it is referring to the correct cell. Check again that the Format option within your Conditional Formatting condition is set to what you thought you'd set (bold, or whatever). If the formula looks as you expected, are you sure that A1 contains a real Excel date (and not a text string that looks like a date)? If you temporaily reformat the cell as General, you should see a number between 39457 and 39485 if it is to satisfy your test. -- David Biddulph "Garza" wrote in message ... This isn't working for me. What could I be doing wrong? "Bernard Liengme" wrote: Select the range of cells to be formatted Use the command Format | Conditional Formatting In the dialog, specify: Formula Is : =AND(A1TODAY(),A1-TODAY()<30) and click the Format button the dialog to make bold and/or colour the cells) NOTE: in this formula change A1 to the first (the one that is uppermost to the left) cell in your range best wishes and Happy New Year -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Donna" wrote in message ... I am new to Excel (obviously), and I have created a simple worksheet that contains numerous dates. I wish to have the dates become bold when they are to expire within 30 days. Is there a formula for that purpose? Thanks... |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Can I get Excel to automatically BOLD dates 30d before expirat
A couple of minor corrections to what Gord said.
If you are starting with 1/1/08 in A1, the rows which will show coloured (as today is Jan 10th) will be 11 to 39, not 1 to 30. And with the formula as specified, the cells coloured would be B11:B39. If you want the whole of B11:E39 coloured, change the formula from =AND(A1TODAY(),A1-TODAY()<30) to =AND($A1TODAY(),$A1-TODAY()<30) to keep the column reference absolute, rather than relative. -- David Biddulph "Gord Dibben" <gorddibbATshawDOTca wrote in message ... One more time............no formula is entered "in a cell" so there is no cell to change. With "real" dates in A1 to A100 starting with January 1, 2008 Make sure they are real dates and not text. Select B1:E100 FormatConditional FormattingFormula is: =AND(A1TODAY(),A1-TODAY()<30) entered in the dialog box. Format to a nice color and OK your way out. A1:A30 should be colored. If not, check the formula in CF to be sure it is exactly as written. As David points out, Excel will sometimes change things. When I pasted =AND(A1TODAY(),A1-TODAY()<30) in the Formula is dialog, Excel altered it to ="=AND(A1TODAY(),A1-TODAY()<30)" which would not work. Gord Dibben MS Excel MVP On Wed, 9 Jan 2008 14:46:25 -0800, Garza wrote: It is still not taking it... So if it's column B to E what should I change the cell to say.. Sorry I really do appreciate your help on this. "David Biddulph" wrote: Firstly, go back into Format/ Condition Formatting/ Formula Is, and check that the formula is what you intended it to be. Excel has a habit of changing things, particularly if you forgot to enter the = sign at the start of the formula. Check also that it is referring to the correct cell. Check again that the Format option within your Conditional Formatting condition is set to what you thought you'd set (bold, or whatever). If the formula looks as you expected, are you sure that A1 contains a real Excel date (and not a text string that looks like a date)? If you temporaily reformat the cell as General, you should see a number between 39457 and 39485 if it is to satisfy your test. -- David Biddulph "Garza" wrote in message ... This isn't working for me. What could I be doing wrong? "Bernard Liengme" wrote: Select the range of cells to be formatted Use the command Format | Conditional Formatting In the dialog, specify: Formula Is : =AND(A1TODAY(),A1-TODAY()<30) and click the Format button the dialog to make bold and/or colour the cells) NOTE: in this formula change A1 to the first (the one that is uppermost to the left) cell in your range best wishes and Happy New Year -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Donna" wrote in message ... I am new to Excel (obviously), and I have created a simple worksheet that contains numerous dates. I wish to have the dates become bold when they are to expire within 30 days. Is there a formula for that purpose? Thanks... |
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Can I get Excel to automatically BOLD dates 30d before expirat
Thanks David.
Gord On Thu, 10 Jan 2008 10:57:35 -0000, "David Biddulph" <groups [at] biddulph.org.uk wrote: A couple of minor corrections to what Gord said. If you are starting with 1/1/08 in A1, the rows which will show coloured (as today is Jan 10th) will be 11 to 39, not 1 to 30. And with the formula as specified, the cells coloured would be B11:B39. If you want the whole of B11:E39 coloured, change the formula from =AND(A1TODAY(),A1-TODAY()<30) to =AND($A1TODAY(),$A1-TODAY()<30) to keep the column reference absolute, rather than relative. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i make certain words automatically bold themselves. | New Users to Excel | |||
Conditional formate:show by color when expiration dates are overdu | Excel Discussion (Misc queries) | |||
Over keying a formula - Can I set this to automatically bold ? | Excel Worksheet Functions | |||
sheet with expiration dates | Excel Discussion (Misc queries) | |||
Are sub-totals able to be set to be bold automatically? | Excel Discussion (Misc queries) |