Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Possible to: Tick or cross off dates as they go by?
For example (lets say all cells have black text):
06 Aug 2007 07 Aug 2007 08 Aug 2007 If today is 06 Aug 2007, nothing happens, all cells have black text. Go to sleep... now its 07 Aug 2007. Is there a way to cross/tick off the previous days automatically with a formula or conditional format? I want to make the text turn to red, eg. in our above example 06 Aug 2007 should now turn red because we are now 07 Aug 2007. Thanks in advance for some replies!! (I hope) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Possible to: Tick or cross off dates as they go by?
Highlight the **entire** column that the dates are in and click on
Format/ConditionalFormatting in Excel's menu bar. Change the first drop down box to "Formula Is" and put this formula in the formula field... =A1<NOW() Put the actual Column letter for the column containing the dates in place of the "A" (in A1) that I used. Rick "Ben" wrote in message ... For example (lets say all cells have black text): 06 Aug 2007 07 Aug 2007 08 Aug 2007 If today is 06 Aug 2007, nothing happens, all cells have black text. Go to sleep... now its 07 Aug 2007. Is there a way to cross/tick off the previous days automatically with a formula or conditional format? I want to make the text turn to red, eg. in our above example 06 Aug 2007 should now turn red because we are now 07 Aug 2007. Thanks in advance for some replies!! (I hope) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Possible to: Tick or cross off dates as they go by?
That's what the conditional format is made for.
Assuming that the date is in A1, choose as condition1 "Formula is" and input =A1<TODAY() Now select the format for this condition (e.g. red text). Copy/paste this formula to all your other cells containing dates (easily done with the format painter) Cheers, Joerg Mochikun "Ben" wrote in message ... For example (lets say all cells have black text): 06 Aug 2007 07 Aug 2007 08 Aug 2007 If today is 06 Aug 2007, nothing happens, all cells have black text. Go to sleep... now its 07 Aug 2007. Is there a way to cross/tick off the previous days automatically with a formula or conditional format? I want to make the text turn to red, eg. in our above example 06 Aug 2007 should now turn red because we are now 07 Aug 2007. Thanks in advance for some replies!! (I hope) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Possible to: Tick or cross off dates as they go by?
Thanks for the quick reply and excellent advice. It is perfect!
So I can learn to understand it. I know what '=A1' is for but can you please tell me what the '<' and '()' are mean? "Joerg Mochikun" wrote in message ... That's what the conditional format is made for. Assuming that the date is in A1, choose as condition1 "Formula is" and input =A1<TODAY() Now select the format for this condition (e.g. red text). Copy/paste this formula to all your other cells containing dates (easily done with the format painter) Cheers, Joerg Mochikun "Ben" wrote in message ... For example (lets say all cells have black text): 06 Aug 2007 07 Aug 2007 08 Aug 2007 If today is 06 Aug 2007, nothing happens, all cells have black text. Go to sleep... now its 07 Aug 2007. Is there a way to cross/tick off the previous days automatically with a formula or conditional format? I want to make the text turn to red, eg. in our above example 06 Aug 2007 should now turn red because we are now 07 Aug 2007. Thanks in advance for some replies!! (I hope) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Possible to: Tick or cross off dates as they go by?
Hi Ben,
conditional formatting:- Assuming cell A1 is the first cell to format then select A1 then select conditional formatting and in conditional formatting select the formula option. In the box insert this formula:- = A1<today() Then select the type of formatting required. Copy the cell and Paste Special- Formats to other cells where you require it. Regards, OssieMac "Ben" wrote: For example (lets say all cells have black text): 06 Aug 2007 07 Aug 2007 08 Aug 2007 If today is 06 Aug 2007, nothing happens, all cells have black text. Go to sleep... now its 07 Aug 2007. Is there a way to cross/tick off the previous days automatically with a formula or conditional format? I want to make the text turn to red, eg. in our above example 06 Aug 2007 should now turn red because we are now 07 Aug 2007. Thanks in advance for some replies!! (I hope) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Possible to: Tick or cross off dates as they go by?
TODAY is a built-in spreadsheet function which returns the current date (the
NOW function I posted in my reply, also a built-in spreadsheet function, returns the current date and time)... the open and closing parentheses are required syntax for these functions (there are no arguments to these function so the parentheses are empty). The < symbol is the "less than" operator (which is what you requested). For conditional formatting, you specify the starting cell (the A1) and Excel automatically performs the copy down through the cell you had selected when you called the conditional formatting dialog. Hence, the A1 becomes (behind the scenes) A2 in the second cell down, A3 in the cell below that and so on throughout the cells you had selected. Each one of those cell's content will be tested to see if it is less than "today" and, if it is, the condition you specified (red text in this case) will be applied. Rick "Ben" wrote in message ... Thanks for the quick reply and excellent advice. It is perfect! So I can learn to understand it. I know what '=A1' is for but can you please tell me what the '<' and '()' are mean? "Joerg Mochikun" wrote in message ... That's what the conditional format is made for. Assuming that the date is in A1, choose as condition1 "Formula is" and input =A1<TODAY() Now select the format for this condition (e.g. red text). Copy/paste this formula to all your other cells containing dates (easily done with the format painter) Cheers, Joerg Mochikun "Ben" wrote in message ... For example (lets say all cells have black text): 06 Aug 2007 07 Aug 2007 08 Aug 2007 If today is 06 Aug 2007, nothing happens, all cells have black text. Go to sleep... now its 07 Aug 2007. Is there a way to cross/tick off the previous days automatically with a formula or conditional format? I want to make the text turn to red, eg. in our above example 06 Aug 2007 should now turn red because we are now 07 Aug 2007. Thanks in advance for some replies!! (I hope) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Possible to: Tick or cross off dates as they go by?
Oh... one step further.
What you guys helped me with is the 3rd condition (again Thanks!). The first 2 are also set to change the colour of the text, ie: 1st Cond - turns text red if a predetermined cell has a 'T' in it, 2nd Cond - turns text blue when 'R' is inserted. Can something be done to make this condition (3rd) override the first two regardless of whether they hold true or not? "Ben" wrote in message ... Thanks for the quick reply and excellent advice. It is perfect! So I can learn to understand it. I know what '=A1' is for but can you please tell me what the '<' and '()' are mean? "Joerg Mochikun" wrote in message ... That's what the conditional format is made for. Assuming that the date is in A1, choose as condition1 "Formula is" and input =A1<TODAY() Now select the format for this condition (e.g. red text). Copy/paste this formula to all your other cells containing dates (easily done with the format painter) Cheers, Joerg Mochikun "Ben" wrote in message ... For example (lets say all cells have black text): 06 Aug 2007 07 Aug 2007 08 Aug 2007 If today is 06 Aug 2007, nothing happens, all cells have black text. Go to sleep... now its 07 Aug 2007. Is there a way to cross/tick off the previous days automatically with a formula or conditional format? I want to make the text turn to red, eg. in our above example 06 Aug 2007 should now turn red because we are now 07 Aug 2007. Thanks in advance for some replies!! (I hope) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Possible to: Tick or cross off dates as they go by?
Make the 3rd condition the 1st condition. The conditions have precedence in
the order in which they are entered. -- Biff Microsoft Excel MVP "Ben" wrote in message ... Oh... one step further. What you guys helped me with is the 3rd condition (again Thanks!). The first 2 are also set to change the colour of the text, ie: 1st Cond - turns text red if a predetermined cell has a 'T' in it, 2nd Cond - turns text blue when 'R' is inserted. Can something be done to make this condition (3rd) override the first two regardless of whether they hold true or not? "Ben" wrote in message ... Thanks for the quick reply and excellent advice. It is perfect! So I can learn to understand it. I know what '=A1' is for but can you please tell me what the '<' and '()' are mean? "Joerg Mochikun" wrote in message ... That's what the conditional format is made for. Assuming that the date is in A1, choose as condition1 "Formula is" and input =A1<TODAY() Now select the format for this condition (e.g. red text). Copy/paste this formula to all your other cells containing dates (easily done with the format painter) Cheers, Joerg Mochikun "Ben" wrote in message ... For example (lets say all cells have black text): 06 Aug 2007 07 Aug 2007 08 Aug 2007 If today is 06 Aug 2007, nothing happens, all cells have black text. Go to sleep... now its 07 Aug 2007. Is there a way to cross/tick off the previous days automatically with a formula or conditional format? I want to make the text turn to red, eg. in our above example 06 Aug 2007 should now turn red because we are now 07 Aug 2007. Thanks in advance for some replies!! (I hope) |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Possible to: Tick or cross off dates as they go by?
Thanks, it now does exactly what I need it to do. You guys are soooo
helpful!! "T. Valko" wrote in message ... Make the 3rd condition the 1st condition. The conditions have precedence in the order in which they are entered. -- Biff Microsoft Excel MVP "Ben" wrote in message ... Oh... one step further. What you guys helped me with is the 3rd condition (again Thanks!). The first 2 are also set to change the colour of the text, ie: 1st Cond - turns text red if a predetermined cell has a 'T' in it, 2nd Cond - turns text blue when 'R' is inserted. Can something be done to make this condition (3rd) override the first two regardless of whether they hold true or not? "Ben" wrote in message ... Thanks for the quick reply and excellent advice. It is perfect! So I can learn to understand it. I know what '=A1' is for but can you please tell me what the '<' and '()' are mean? "Joerg Mochikun" wrote in message ... That's what the conditional format is made for. Assuming that the date is in A1, choose as condition1 "Formula is" and input =A1<TODAY() Now select the format for this condition (e.g. red text). Copy/paste this formula to all your other cells containing dates (easily done with the format painter) Cheers, Joerg Mochikun "Ben" wrote in message ... For example (lets say all cells have black text): 06 Aug 2007 07 Aug 2007 08 Aug 2007 If today is 06 Aug 2007, nothing happens, all cells have black text. Go to sleep... now its 07 Aug 2007. Is there a way to cross/tick off the previous days automatically with a formula or conditional format? I want to make the text turn to red, eg. in our above example 06 Aug 2007 should now turn red because we are now 07 Aug 2007. Thanks in advance for some replies!! (I hope) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checkbox to be marked with a cross rather than a tick | New Users to Excel | |||
tick box | Excel Discussion (Misc queries) | |||
cannot find cross and tick box just below the toolbars | Setting up and Configuration of Excel | |||
tick | Excel Discussion (Misc queries) | |||
tick box | Excel Discussion (Misc queries) |