how can I format a cell to change on a specific date

Can a cell that contains text, be formatted to change font color on a future 
date?  The column is reading "Active" in green to indicate that the contract 
for that item is still valid, but I would like to change the "Active" to red 
font, or possibly change the text to read "Inactive" in red.  It's the color 
of the font that's important, and the date that I want it to change on is on 
9/11/08.

thanks for your help.
0
ZC (1)
9/14/2007 6:48:01 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
438 Views

Similar Articles

[PageSpeed] 21

Try conditional formatting

Assume the data is in A1 down

Select col A (A1 active), 
then apply conditional formatting using Formula is:
=AND(A1="Active",TODAY()>= --"11 Sep 2008")
Format the font to taste, ok out

The above will format only the cells in col A which contain the text 
"Active" once the set "future" date: 11 Sep 2008 is reached. To test that the 
CF works, just change the date to a current date, eg:      
=AND(A1="Active",TODAY()>= --"15 Sep 2008")

And if you really want the format to trigger only for that specific date, 
drop the ">", use instead:
=AND(A1="Active",TODAY()= --"11 Sep 2008")

Modify to suit ..
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"ZC" wrote:
> Can a cell that contains text, be formatted to change font color on a future 
> date?  The column is reading "Active" in green to indicate that the contract 
> for that item is still valid, but I would like to change the "Active" to red 
> font, or possibly change the text to read "Inactive" in red.  It's the color 
> of the font that's important, and the date that I want it to change on is on 
> 9/11/08.
> 
> thanks for your help.
0
demechanik (4694)
9/14/2007 11:16:02 PM
Typo correction:
> To test that the CF works,
> just change the date to a current date, eg:
> =AND(A1="Active",TODAY()>= --"15 Sep 2008")

The test formula should have read as (but of course<g>):
=AND(A1="Active",TODAY()>= --"15 Sep 2007")
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---  


0
demechanik (4694)
9/16/2007 12:07:41 AM
Reply:

Similar Artilces:

formatting dates by year #2
I would like to know if there is a way to format dates so that they are formatted by year only. For example, if I click on Format -> Cells, there are a bunch of options, but none of them list to show the year only, just mm/dd/yy or mm/yy, etc. I am trying to create a chart so that only the year shows up. Also, I have tried doing Custom->and "yyyy" in type, but only 1 specific year shows up (i.e. 1905) for all the values. I would like to keep my values. Thanks much! ...

Vlookup return most recent date (value)
Hello, I have a set of data where Column A contains an personal ID# (001). Column B contains a timestamp. My table has multiple entries for each ID# and different timestamps for each entry. I want to use a vlookup to find the MOST RECENT timestamp for EACH ID. Any help would be greatly appreciated. Thanks, Ryan With your data in ColA/B and the query ID in cell C1 try the below =SUMPRODUCT(MAX(($A$2:$A$100=C1)*$B$2:$B$100)) -- Jacob (MVP - Excel) "ryanholliday" wrote: > Hello, > > I have a set of data where Column A contains an personal I...

conditional formatting sort
Is there a way to sort after conditional formatting. So, if I've set up a conditional format to color my text red if the number is between 0 and 95, then I want to sort so all red numbers are at the top of the spreadsheet? Any ideas? Thanks for your time and assistance. Roberta On Thu, 25 Oct 2007 05:08:00 -0700, rrupp <rrupp@discussions.microsoft.com> wrote: You can't sort by color, but you can add a column to your query like this: SortColumn: Iif(SomeValue >= 0 and SomeValue <= 95, 1, 2) Then sort by this column. -Tom. >Is there a way to sort after condition...

Changing Cells and entering data in them
Thanks for the help again. Big thanks to Steve you've got me this far. I went out and bought a book, but it's like reading a foreign language. I was informed today that I can't have message boxes come up. I need to have the code point at the cells and if they are blank turn which ever one is blank red or if both are then both turn red then pause for each cell to be filled in. Cell F14 "Last Name" then automatically go to Cell F16 "First Name" on tab or enter. Basically if Cell F22 or F23 has an X in it, Cells F14 an F16 turn red and cell F14 has the focus...

Need Help, Task Start Date is wrong
I’m using MS Project 2007, have several task linked with finish to start. I have set date to schedule from, hours per day set to 8 and Working Monday thru Friday. My schedule shows Task 1 Duration 4 days, start Wed 6/2/10, Finish Mon 6/7/10 Task 2 Duration 3 days, start Mon 6/7/10, Finish Thu 6/10/10 Task 2 should have a Start Date of 6/8/10 not 6/7/10; what is causing this? Thanks in advance for your help. ...

Change position ID in HR
We would like to change the position ID in human resources. Does anyone have a suggestion on this. You would need to do it behind the scenes using a tool like Query Analyzer. -- Charles Allen, MVP "KT" wrote: > We would like to change the position ID in human resources. Does anyone have > a suggestion on this. careful though when you change it on the background as you need to know all the tables that use this position ID or Position Code and change it there too otherwise all the link would be gone and you end up with orphan records that its just the same as creatin...

Is anyone an expert with outlook that I can call on the phone?
How do I share calenders between outlook and my MSN Premier account? Assuming you are using the outlook connector, you need to set the msn account to be the default message store then outlook will use the msn calendar as the default. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM You can access this...

Moving incoming mail automatically to specific folders.
I have three separate E-Mail Accounts (1,2, and 3) I have created three subfolders under Inbox (Mail1, Mail2, Mail3) How can I automatically get the mail sent to the correct Mail in location (Mail1,Mail2 or Mail3). I am sure the answer like everything is easy when you know how but I have just spent an hour trying! Thanks Ted On Mon, 2 Jan 2006 00:24:44 -0000, "EddyStone" <teds@screaming.net> wrote: >I have three separate E-Mail Accounts (1,2, and 3) >I have created three subfolders under Inbox (Mail1, Mail2, Mail3) >How can I automatically get the mail sent to ...

default values in a cell
Hello, can you help me please Cell B1 contains a complex mathematical formula which requires (in several places) a number from cell A1. Cell A1 can contain any integer number, but it is usually the same (30). I would like to be able to leave cell A1 empty, and only enter a number when it is not 30 , ie the default value of A1 is 30, unless specified. How do I go about this ? Should I look at conditional formatting, or put lots of IF functions into an already complex formula? Thank as always KK Use 2 cells Modify the complex formula to use B1 rather than A1 ( or any other un-u...

Show date, time & day of week in one cell
Can I show the date, time and day of week in one cell. I have: 09/03/07 8:30 AM in one cell using the format (Format/Cells/Custom): [$-409]mm/dd/yy h:mm AM/PM;@ Excel refuses to accept ddd for Mon or dddd Monday at the end of the format I want it to read: 09/03/07 8:30 AM Monday in 1 cell. I have Excel 2003. One way: mm/dd/yy h:mm AM/PM dddd;@ In article <Xns99B8A3CAF9130pencilunlistedcom@208.49.82.220>, Burp <burp@beep.comINVALID> wrote: > Can I show the date, time and day of week in one cell. > > I have: > 09/03/07 8:30 AM > in one cell using the for...

Fixing a formula to read a general format?
A few months ago I had asked a question and got this answer: Question: I have a a set of values in A1 through A100. I need to look up each value and find a match in another set of values located in C1 through C200. If a match is found then I need the formula located in column B to return the value in the same row but the next column over (D). Answer: =IF(ISNUMBER(VLOOKUP(A1,$C$1:$D$200,2,0)), VLOOKUP(A1,$C$1:$D$200,2,0), "") Copy down through A100. The formula worked fine, however the reference value and the look up value are not any more numbers but a combinatio...

Business Portal Development
All: I know how to use Rational XDE, VS and the BP SDK to create new entities in BP, but how does one go about chaning labels? For example, if I wanted to change two labels on a requisition mgt screen: Can I just crack open the ASPX page and change the caption property? Thanks, Dwight -- Hi, To edit the lables you can use front page server extensions 2003. "Dwight Specht" wrote: > All: > > I know how to use Rational XDE, VS and the BP SDK to create new entities in > BP, but how does one go about chaning labels? > > For example, if I wanted to ch...

How do I get Excell to sort dates that range from 1800 to 1900's
I have posted a cemetery survey on Excell spreadsheet. When I try to sort the birth dates or death dates in ascending or descending order the range of 1900 dates will sort but the 1800 dates fall after the 1900 and will not sort. Why? Is this inherit with this program? The WinXL default dates start with 31 December 1899, so earlier "dates" are interpreted as Text. You can use helper columns and text functions (LEFT(),RIGHT(),MID()) to put the Year, Month and Day in separate columns, then sort. Take a look here for additional workarounds: http://j-walk.com/ss/excel/files...

How do you change the APR?
How do you change the APR in a Credit Card account? Like if you have to change it from 13.250% to 14.240% Depends on the version but (in M2007) go to the CC Account and click on Change Account Settings. Scroll down to Credit Information and click on Change Credit Details. If you want to change the Minimum payment rate/amount as well, click on the Minimum payment amount and a side window opens. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. I do not respond to any emails that I have not specifically ...

Call & Place Graphic Based on Cell Value?
Is there any way to call & place a graphic image based upon a cell value? Maybe you can look at J.E. McGimpsey's page: http://www.mcgimpsey.com/excel/lookuppics.html documike wrote: > > Is there any way to call & place a graphic image based upon a cell value? -- Dave Peterson ...

Can I share entities in migration process?
The entities in CRM have only one owning user. If I want=20 who a user see a entity of another user, the entity must=20 be shared with this user.=20 Well, I want migrate entities who must be shared with some=20 users. Exists a way to do it? Thank you for pay attention and sorry my bad english. []'s Vin=EDcius Pitta Lima de Ara=FAjo ...

After editing example1.xls and click SAVE, the filename changes to AABBEE.xls
After editing example1.xls and click SAVE, the filename changes to AABBEE.xls. The original file still exist and has been updated but the 'funny' filename also have the same content, and editable. The example1.xls is stored in a server and accessed by many people within the company. Everyone accessing to the file will change the filename unknowingly after saving it. ...

Can't do adjustment in analytical accounting
I am trying to change an analytical assignmnet through the Edit Analysis. When I try and change it, I receive an error stating "The Code of this Transaction Dimension cannot be adjusted". Has anyone seen this before? I have done similar changes many times, but I don't know why I can't now. Any insight would be helpful. Thanks, KJ Hi KJ What version of Gp are you running? I haveseen this happen in 8 but one of the service packs fixed it. (I think it was SP 4 or 5) Fliehigh "KJ" wrote: > I am trying to change an analytical assignmnet through the Edit >...

number format with leading 0
Dose anyone know how to create a number format that would show a leading zero for the numbers 0-9. That is, so that 1 would show as 01. I can do with a text format but would like to retain the integrity of the number. Thanks Try this: Format>Cells>Number Category: Custom Type: 00 Click [OK] Does that help? *********** Regards, Ron "Darby" wrote: > Dose anyone know how to create a number format that would show a leading zero > for the numbers 0-9. That is, so that 1 would show as 01. I can do with a > text format but would like to retain the integrity...

Date/Day
Hi, How do I make Excell tell me the day that the Date corresponds to. Example: If I have 8/7/03 in a column, I want to be able to enter a formula in the next column such that Excell would tell me it is Thursday Thx Omer Omer, Look at the WEEKDAY function. From Help Syntax WEEKDAY(serial_number,return_type) Serial_number is a sequential number that represents the date of the day you are trying to find. Dates may be entered as text strings within quotation marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers (for example, 35825, which represents...

Changing Domain Name through Internet Address Management Wizard
Hi Guys I know its weekend. but i need to post for peace of mind. I have a client using SBS 2008 STD.He isusing remote.mydomain.com address through Internet address management wizard. Now he wants to change to remote.mynewdomain.com as old domain is too long. Is there any way so we can change through IAMW. run the wiz, job done. "Atul Sood" <atulsood@live.com> wrote in message news:uVlbmkd3KHA.4028@TK2MSFTNGP05.phx.gbl... > Hi Guys > > I know its weekend. but i need to post for peace of mind. I have a client > using SBS 2008 STD.He isusing rem...

How can I change text to proper text in multiple cells.
I need to change names that are all in caps to proper case in 100 cells. If I click each one individually, it works, but I need to be able to perfomr this funcion automatically on all the cells. One other post said to be sure calc is set to automatic and mine is. Any instruction is most appreciated. Thank you. Insert a helper column to the right of the column with the names. Then use a formula like: =proper(a1) and drag down that column Then select that column edit|copy select the original range Edit|paste special|Values And then delete the helper column. bethye99 wrote: > > I...

can't open new worksheets in a separate window
I've been trying to open multiple wookbooks in Excel, but they won't open in a new window. Instead they continue to open in the same window - one on top of the other. I've tried playing around with the maximize and restore, but to no avail. I tried right clicking on the Excel button in the taskbar to cascade the windows, but the pop-up menu is inactive. any advice would be greatly appreciated. Running Excel 2003, Windows XP. This might be it: Tools - Options - View tab - Show: Windows in taskbar. Earl Kiosterud www.smokeylake.com "3R's" <3R's@di...

Can validation lists be enlarged
When a validtion list is created can this be enlarged in a spreadsheet which has been reduced to view. Hi for a workaround see: http://www.contextures.com/xlDataVal08.html#Larger -- Regards Frank Kabel Frankfurt, Germany "AC" <AC@discussions.microsoft.com> schrieb im Newsbeitrag news:B6A557E7-7B2C-459B-94CE-02606B6CAB3A@microsoft.com... > When a validtion list is created can this be enlarged in a spreadsheet which > has been reduced to view. ...

Remote humans can't get email addressed to them from within the do
I call them remote humans because they are not "users" set up on the SBS2003 system. I've set up a local domain, call it domain.local, and the email domain is domain.org. For all internal users, there is no problem. But there are two people, out of state, with email addresses user1@domain.org and user2@domain.org. Our email is hosted, and we use the POP3 connector to bring it into our Exchange server. We do not use POP3 connector for the remote humans - their Outlook client has the POP3 account set up on it. These remotes will never connect to the local domain, all th...