Automatic Date Update

i am in need of a formula that will compare the current date with th
date in another cell.  if the date in the other cell is past th
current date, i want that cell to be highlighted in Red.  For example
if the date in the cell is June 10, 2005 and today's date is June 22
2005, i want excel to turn that June 10, 2005 cell Red.  Also i
possible can i tell excel to turn the cell Yellow if that date HAS NO
passed but is approaching (say within 30 days)?  Can someone pleas
tell me 1. if any of this is possible, and 2. what formulas i need t
accomplish this. Thanks

--
khaji0
-----------------------------------------------------------------------
khaji00's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2454
View this thread: http://www.excelforum.com/showthread.php?threadid=38143

0
6/22/2005 10:25:27 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
632 Views

Similar Articles

[PageSpeed] 49

For this example, the input cell is A1 and the compare cell is B1

A1: 06/22/2005
B1: 06/10/2005

Select A1
Format>Conditional Format
->Condition 1
--->Cell value is greater than =$B$1
--->Format: Pattern:RED, Font Color:White
->Condition 2
--->Cell value is between =$B$1-30 and =$B$1
--->Format: Pattern:YELLOW, Font Color:Black
Click [OK]

That should do it....
Does it?

Ron


-- 
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21419
View this thread: http://www.excelforum.com/showthread.php?threadid=381433

0
6/22/2005 10:46:10 PM
Good evening Khaji00

This is possible, but a bit difficult to explain, so, loath as I am to
doing so, I have attached a small file demonstating how to.  You need
to use the conditional formatting tool as shown in the example file.

HTH

DominicB


+-------------------------------------------------------------------+
|Filename: Book2.zip                                                |
|Download: http://www.excelforum.com/attachment.php?postid=3526     |
+-------------------------------------------------------------------+

-- 
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=18932
View this thread: http://www.excelforum.com/showthread.php?threadid=381433

0
6/22/2005 10:48:14 PM
Reply:

Similar Artilces:

How do I get an automatic equal sign to begin the formula bar?
How do I get an automatic equal sign to appear in the formula bar in Excel for Microsoft 2003? This happened for me in the 2000 version and I could just click on it with my mouse pointer, but now all that appears is the function sign with no equal sign after it. It makes my job a lot more time consuming to have to type in the equal sign while building formulas. Please help, anybody? The = icon disappeared in newer versions of excel (xl2002???). You can add an = icon Tools|customize|commands tab|Insert category (Scroll down the list until you find the = icon) And drag it to your fav...

Multicurrency Exchange Rate Dates
Which date is used to determine the exchange rate - the transaction's Document date or the Posting date? Hi Elaine, In Multicurrrency transaction, document date is used to validate the exchange rate to be use. Posting date is actually the document date that will appear in GL if the transactions originated from sub-ledger. Hope this helps! Dennis Araullo, MACS MCTS GP,CRM,AX MCTIP Database Administrator 2008 CCEA "Elaine" wrote: > Which date is used to determine the exchange rate - the transaction's > Document date or the Posting date? Th...

Date last filled...
Hi all :-) Access 2007, Vista Ultimate SP2 32 bit I have a data entry form for entering prescription information. In need = to be able to enter the date that the existing prescription was last = filled. . I am in need of a means to look up the name of the prescription selected = in the combo box for that field, and display the date that prescription = was last filled for the new entry before it is saved. I am not sure how = best to have it look up the data in the table.=20 Field name in the table is LastFilled and is a Date/Time data type. The = current ControlSource is the ...

Can I have an email message automatically?
In Outlook 2003 I want to automatically send email messages at preset times of the day. Can this be done. do you want to send a predefined message or just have outlook connect and send all waiting messages at a specific time? for the first: http://www.outlook-tips.net/archives/2004/20041230.htm for the second: neither outlook nor windows supports it directly, but there is (or was) at least one program that can do it - I don't know if it's still available or recall the name. :( You could also use task scheduler to open and close outlook at preset times and if it's configu...

How do you automatically play a song when another one finishes?
How do you automatically play a song when another one finishes? You need to explain exactly what you need. Specifically Version of Powerpoint and are the sounds on the same slide or do you mean play one file across slides till it ends then play the other. -- john ATSIGN PPTAlchemy.co.uk Free PPT Hints, Tips and Tutorials http://www.pptalchemy.co.uk/powerpoint_hints_and_tips_tutorials.html "I need help urgently!" wrote: > How do you automatically play a song when another one finishes? ...

current date and time object
Hi, Is there a way to have a current date and time box somewhere on a sheet? I know the =now() function does it in a cell, but it only refreshes on macro runs etc. I'm looking for something real time in the sheet. Any ideas? Thanks! --Randy Starkey Here is one example Just paste the code in a general code module, name a cell 'clock' (menu Insert>Name>Define) and then run the 'StartClock' macro. Option Explicit Private Declare Function FindWindow Lib "user32" _ Alias "FindWindowA" _ (ByVal lpClassName As String, _ ByVal lpWin...

automatic sent mail forwarding
is this possible at all??!! the set up at the moment is that mail comes in to public folders and the relevant users can reply to these emails as the public folder email address. the next thing we want it for all sent mail from these public folders to automatically go to another folder and not the users sent items folder in outlook. I know you can BCC the public folder when sending the email but "them that B" would like it automated because it relies on someone remembering to add the email to the BCC field Any ideas?? Cheers Ears PeterP wrote: > is this possible at al...

gp10 end of the year update
-- Carol ...

More than two Recipient Update Services
I have noticed that my Exchange 2003 servers have 3 Recipient update services. One is the Enterprise one, but the other two are just Recipient Update Service and Recipient Update Service 1. Why would I have two of them, can can that cause problems? They are pointing to two different domain controllers and we only have a single domain in our forest. Thanks in advance Marc On Fri, 20 Jan 2006 06:20:02 -0800, "Marc" <Marc@discussions.microsoft.com> wrote: >I have noticed that my Exchange 2003 servers have 3 Recipient update >services. One is the Enterprise one, b...

Invalid year '6' in date function....
When I try to print a "Checkbook Balance Inquiry Report" from the "Checkbook Balance Inquiry" display, I get the following error message: Unhandled script exception: Invalid year '6' in date function. Script terminated. EXCEPTION_CLASS_SCRIPT_OUT_OF_RANGE Only part of the report prints, but what's there is correct, just cut off. It seems to have something to do with a voided transaction in the register.... I've search both these forums and customer source's Knowledge Base and I can't find anything about it. Does anyone know the solution to th...

notify a public folder poster when item is updated
Have public folder with custom form to log customer complaints. Anyone can post an item, but the status field is restricted and can only be changed by specific users. e.g. Approved for action, Fix is live, Denied. I'm trying to find a way to use the Folder Assistant to reply to the original poster, when their item has been updated. flow would be: user posts complaint super users notified (can do this already with simple forward) Super user opens item, reseaches and changes status super user saves item with updated status user receives email - your complain has been updated. I seem to ...

Money 2007 not downloading sp2 update after re-install
I just completed rebuilding my computer using the Windows 7 beta. After reinstalling Money 2007 I can no longer open my account file as the versions are different. Money is not announcing a new version as it did before. Is there a way to force this or download the update manually? Thanks, One thing to try is opening the sample.mny file to see if it will download anything. Otherwise you are out of luck. After all Windows 7 is a beta and all that this implies i.e. full of bugs and not tested with all MS applications. If the sample.mny trick does not work you will need to scrub Window...

Cannot install downloaded updates for freshly-installed Office 2004
I recently uninstalled Office 2008 out of sheer disgust with its sluggish performance and many irritating features that have to be overridden by resetting preferences. I re-installed Office:Mac 2004 student and teacher edition from my licensed disk. However, there was no registration process and I was never asked to type in my product key. Then, when I download the updates (of which there are 4 that must be installed in order) I found I could not install them. Every time I tried, I received an error message saying that the installer could not locate the correct programs. I recently updat...

<Date() not working in database converted from access97 to xp
Can anyone help? In a query I have <date() as a criteria for a field... this is fine in access97 but in xp it does not work at all.... I would imagine that this expression has changed between versions of access but does anyone know how I set the criteria to show records older than the current date? Thanks Frazer The function has not changed, but it is probably a problem with references. Details: http://members.iinet.net.au/~allenbrowne/ser-38.html -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to grou...

#How to update a worksheet only after N interactions of DO ..LOOP cycle?? URGENT!!!
I get de following macro associated in a command button: Private Sub cmdIniciar_Click() Sheets("Folha1").Select Do tryes = tryes + 1 Sheets("Folha1").Range("b10").Value = tryes If Sheets("Folha1").Range("b14").Value = True Then End End If Loop End Sub The purpose is to produce one random aleatory data list, the problem is that Excel 2003 refreshes permanently the aleatory cell data witch makes consume all processor bandwidth. Want I need is that the active worksheet "Folha1" only refreshes when the variabl...

Allow searching of date fields to allow wildcard characters
For fields such as birthdays and expiry dates, customers often want to search the database for a list of contacts with birthdays. It would be great if you would allow wildcards in date field searches so that we could search for all birthdays that occur in a specific month. The year is not always relevant when performing date searches. Birthdays is a common search performed by customers in the financial sector as well as other industries. Currently, we have to add an additional text field to facilitate this type of search. It adds confusion to the users, in some cases double data entry...

Add a custom date to recipt!
I would like to add a coupon to the bottom of every recipt that I print, but I also need to have a custom date range of when the coupon may be used (one week from the purchase date) within that coupon. A simple text coupon at the bottom of the recipt would be fine, but I need a code that will print the date exactly one week from the date of the transaction. Hi Scott, there are few things You may do, for adding a new date into your recepit. Create a variable into your receipt, for example like this: <SET name="Date2" custom="true" description="Second Dat...

updating frontpage website with new software
Now that Microsoft is not upgrading or providing support for Frontpage, is there a software I can use that will service my website, which was created in FP? I am a very rudimentary user, and mainly need to upload content, but I want to get a web design software that would allow me to make a few changes. However, I don't want to start over. Will new software do this, or would I be better off to try to find an old version of FP? Thanks!! Using Microsoft software there are two choices: 1) Expression Web v3 - Designed for standards compliant cross browser web pages. A 60...

Need help updating a value in VBA code
Hello Great Plains Gurus, I need to write some VBA code that changes the value of a field on another form (SalesCustomerDetailEntry). Easy enough, but the problem that I have is the original form that fires the VB code is in a third party dictionary, and the Dynamics dictionary already references that third party dictionary. Therefore I can not add the Dynamics dictionary as a reference in my third party dictionary due to cyclical references not being allowed. Is there any way around this issue, ie calling some sanscript function to perform the update from inside VBA? I realize that I can...

Applying Patches, Services Pack and Securiy Update
Please confirm on this situation: I have FE-BE environment, 1 FE and 2 BE. One day one of BE server (=BESERVER1)is trouble. I decided to apply patches: KB912442, KB916803, KB911829. First, all services and access to this FE and BE is fine. about an hour or two, user complaint that the OWA is not working. I remember that I should apply the same patches also at FE. then I applied the patches on FE and BESERVER2 (another BE Server). for a while it works allright. but then I got another complaint about OWA access. After several hour work around (rollback patches on FE, and reapply on BE...

My profile pic isn't updating on my other pc's.
A few days ago, using latest WLM, I changed my WLM photo on my laptop. Today, I signed onto WLM on my desktop (also using latest version), but it still shows my old photo instead of my new one. I have to option "use the same display picture and personal message wherever I sign in" checked on both machines. Any ideas why it didn't update my newer photo on my desktop when I signed onto WLM? Greetings, Put simply, it doesn't always work. If for some reason it hasn't got around to sending it to the Messenger server (or the server was down, whatever), it wo...

Excel Automatically Formating Cells?
I am reading a file of decimal numbers into VBA. I can read them into a variant, or into a DOUBLE. No matter. When I assign the value to the cells of a worksheet, the cells are automatically formatted to currency. As a result, I have only two digits of precision. Everywhere else in the program, I do such assignments with no problem. Reformatting the cells outside the program doesn't help.Converting the variant to a string works before the assignment works but... why should I have to do this? Any idea what I am doing wrong? Thanks and Regards, -Larry Curcio Are you making sure that th...

Select Between Two Dates
Hi, I need to create a query to select dates that are between date 1 (1985-11-04) and date 2 (1985-11-11). Currently no records are showing up when I do this: Between (1985-11-4) And (1985-11-11) I've checked and made sure that there is at least one record that meets all specified criteria. Any help would be appreciated. Thanks, jay_2882 Try... Between #1985-11-4# And #1985-11-11# If your dates are in variables, use the DATEDIFF function (look up in Access Help) -Dorian "jay_2882" wrote: > Hi, > I need to create a query to select dates that are between date 1 &...

Using REPT funtion with dates between sheets and workbooks
I am trying to repeat dates in several different places, so that I onl have to type it once. However, when I use the REPT function I get number instead of a date format. I've tried to format the cells back t date function, but it doesn't change -- ROCKWARRIO ----------------------------------------------------------------------- ROCKWARRIOR's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2709 View this thread: http://www.excelforum.com/showthread.php?threadid=46828 The REPT function repeats 'whatever' the number of times you set it to, it wil...

How to add a modified date in header(excel)?
I need to get in header the date when the excel document have last modified. I tried using &[Date] this brings the current date when we open the doc. Kindly suggest good way of doing this. Private Sub Workbook_BeforePrint(Cacel As Boolean) With ActiveWorkbook .ActiveSheet.PageSetup.LeftHeader = "Last saved on: " & _ Format(.BuiltinDocumentProperties("Last Save Time"), "dd mmm yyyy") End With End Sub goes in the ThisWorkbook code module. Watch the wrap-around on the Format line -- HTH RP (remove nothere from the emai...