conditional formatting dates #3

I maintain a "DEMO" spreadsheet that has a return date for the product of 
mm/dd/yyyy. I would like to have that highlighted 7 days before the product 
is due back. Any help with a formula would be greatly appreciated!! Also, is 
there an ability to format so it also emails me a notice?

Thanks,

Phil Hamm
0
Phil1 (164)
7/13/2005 5:04:04 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
469 Views

Similar Articles

[PageSpeed] 8

Hi Phil-

One Option, there are others- In Format>Conditional Formatting:

Cell Value is         Less than or equal to         =NOW()-7

Set your cell format specs to how you want the date to display.

HTH |:>)


"Phil" wrote:

> I maintain a "DEMO" spreadsheet that has a return date for the product of 
> mm/dd/yyyy. I would like to have that highlighted 7 days before the product 
> is due back. Any help with a formula would be greatly appreciated!! Also, is 
> there an ability to format so it also emails me a notice?
> 
> Thanks,
> 
> Phil Hamm
0
CyberTaz (411)
7/13/2005 5:17:03 PM
Use a formula of

=AND(A1<>"",A1>TODAY()-7,A1<TODAY()+1)

to avoid blanks and future dates


-- 
 HTH

Bob Phillips

"CyberTaz" <CyberTaz@discussions.microsoft.com> wrote in message
news:EC9E23AF-D170-438C-8BA7-95906E563BEC@microsoft.com...
> Hi Phil-
>
> One Option, there are others- In Format>Conditional Formatting:
>
> Cell Value is         Less than or equal to         =NOW()-7
>
> Set your cell format specs to how you want the date to display.
>
> HTH |:>)
>
>
> "Phil" wrote:
>
> > I maintain a "DEMO" spreadsheet that has a return date for the product
of
> > mm/dd/yyyy. I would like to have that highlighted 7 days before the
product
> > is due back. Any help with a formula would be greatly appreciated!!
Also, is
> > there an ability to format so it also emails me a notice?
> >
> > Thanks,
> >
> > Phil Hamm


0
phillips1 (803)
7/13/2005 6:08:38 PM
Thanks to both of you for the assist!!!!!

Phil

"Bob Phillips" wrote:

> Use a formula of
> 
> =AND(A1<>"",A1>TODAY()-7,A1<TODAY()+1)
> 
> to avoid blanks and future dates
> 
> 
> -- 
>  HTH
> 
> Bob Phillips
> 
> "CyberTaz" <CyberTaz@discussions.microsoft.com> wrote in message
> news:EC9E23AF-D170-438C-8BA7-95906E563BEC@microsoft.com...
> > Hi Phil-
> >
> > One Option, there are others- In Format>Conditional Formatting:
> >
> > Cell Value is         Less than or equal to         =NOW()-7
> >
> > Set your cell format specs to how you want the date to display.
> >
> > HTH |:>)
> >
> >
> > "Phil" wrote:
> >
> > > I maintain a "DEMO" spreadsheet that has a return date for the product
> of
> > > mm/dd/yyyy. I would like to have that highlighted 7 days before the
> product
> > > is due back. Any help with a formula would be greatly appreciated!!
> Also, is
> > > there an ability to format so it also emails me a notice?
> > >
> > > Thanks,
> > >
> > > Phil Hamm
> 
> 
> 
0
Phil1 (164)
7/13/2005 6:39:02 PM
OK...I'm a rookie at this kind of thing......I'm missing something here....

Column "I" has a heading of "DATE DUE BACK" -- I would like to have I2 thru 
I90 to turn RED 7 days before the date in that cell. I keep looking/typing 
the formula in and trying differnet "minus # of days" to see if it 
changes.....mmmmm........confusion on my end - IDEAS???

Thanks much!!!!

Phil

"Bob Phillips" wrote:

> Use a formula of
> 
> =AND(A1<>"",A1>TODAY()-7,A1<TODAY()+1)
> 
> to avoid blanks and future dates
> 
> 
> -- 
>  HTH
> 
> Bob Phillips
> 
> "CyberTaz" <CyberTaz@discussions.microsoft.com> wrote in message
> news:EC9E23AF-D170-438C-8BA7-95906E563BEC@microsoft.com...
> > Hi Phil-
> >
> > One Option, there are others- In Format>Conditional Formatting:
> >
> > Cell Value is         Less than or equal to         =NOW()-7
> >
> > Set your cell format specs to how you want the date to display.
> >
> > HTH |:>)
> >
> >
> > "Phil" wrote:
> >
> > > I maintain a "DEMO" spreadsheet that has a return date for the product
> of
> > > mm/dd/yyyy. I would like to have that highlighted 7 days before the
> product
> > > is due back. Any help with a formula would be greatly appreciated!!
> Also, is
> > > there an ability to format so it also emails me a notice?
> > >
> > > Thanks,
> > >
> > > Phil Hamm
> 
> 
> 
0
Phil1 (164)
7/24/2005 6:19:12 PM
I believe that you *are* missing COMPLETELY the initial instructions of
Cyber.

<<<"One Option, there are others- In Format>Conditional Formatting:">>>

Which means ... you are to use "Conditional Formatting"!
Which means ... you *don't* put the formula in the cells of Column I!
Is that perhaps what you did?

Anyway, with a quiet Sunday, and the lawn already mowed, would you like *3*
formats for Column I?
One for 7 days before due date,
One for due date,
And one for Past Due.

Start off by selecting I2 to I90.
While the cells are *still* selected, and with the focus of the selection in
I2 (colored white), click:
<Format> <ConditionalFormat>
Change "Cell value is" TO "Formula Is",
And enter this formula:

=AND(I2>0,I2>TODAY()-7,I2<TODAY())

Then click on "Format", and choose your font and pattern colors.
Then <OK>.

Click <ADD>, which allows you to set a second condition and format.
Click "Formula Is", and enter this formula:

=AND(I2>0,I2=TODAY())

Then click on "Format", and choose your font and pattern colors.
Then <OK>.

Click <ADD> again for your final condition and format.
Click "Formula Is", and enter this formula:

=AND(I2>0,I2>TODAY())

Then click on "Format", and choose your last font and pattern colors.
Then <OK> <OK>.

-- 
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Phil" <Phil@discussions.microsoft.com> wrote in message
news:3AA0ADB3-7EDD-44EE-BAFB-BB95F6D26DA3@microsoft.com...
> OK...I'm a rookie at this kind of thing......I'm missing something
here....
>
> Column "I" has a heading of "DATE DUE BACK" -- I would like to have I2
thru
> I90 to turn RED 7 days before the date in that cell. I keep looking/typing
> the formula in and trying differnet "minus # of days" to see if it
> changes.....mmmmm........confusion on my end - IDEAS???
>
> Thanks much!!!!
>
> Phil
>
> "Bob Phillips" wrote:
>
> > Use a formula of
> >
> > =AND(A1<>"",A1>TODAY()-7,A1<TODAY()+1)
> >
> > to avoid blanks and future dates
> >
> >
> > -- 
> >  HTH
> >
> > Bob Phillips
> >
> > "CyberTaz" <CyberTaz@discussions.microsoft.com> wrote in message
> > news:EC9E23AF-D170-438C-8BA7-95906E563BEC@microsoft.com...
> > > Hi Phil-
> > >
> > > One Option, there are others- In Format>Conditional Formatting:
> > >
> > > Cell Value is         Less than or equal to         =NOW()-7
> > >
> > > Set your cell format specs to how you want the date to display.
> > >
> > > HTH |:>)
> > >
> > >
> > > "Phil" wrote:
> > >
> > > > I maintain a "DEMO" spreadsheet that has a return date for the
product
> > of
> > > > mm/dd/yyyy. I would like to have that highlighted 7 days before the
> > product
> > > > is due back. Any help with a formula would be greatly appreciated!!
> > Also, is
> > > > there an ability to format so it also emails me a notice?
> > > >
> > > > Thanks,
> > > >
> > > > Phil Hamm
> >
> >
> >

0
ragdyer1 (4060)
7/24/2005 7:53:26 PM
Thanks for having the lawn mowed already....I'll give it a shot....

"Ragdyer" wrote:

> I believe that you *are* missing COMPLETELY the initial instructions of
> Cyber.
> 
> <<<"One Option, there are others- In Format>Conditional Formatting:">>>
> 
> Which means ... you are to use "Conditional Formatting"!
> Which means ... you *don't* put the formula in the cells of Column I!
> Is that perhaps what you did?
> 
> Anyway, with a quiet Sunday, and the lawn already mowed, would you like *3*
> formats for Column I?
> One for 7 days before due date,
> One for due date,
> And one for Past Due.
> 
> Start off by selecting I2 to I90.
> While the cells are *still* selected, and with the focus of the selection in
> I2 (colored white), click:
> <Format> <ConditionalFormat>
> Change "Cell value is" TO "Formula Is",
> And enter this formula:
> 
> =AND(I2>0,I2>TODAY()-7,I2<TODAY())
> 
> Then click on "Format", and choose your font and pattern colors.
> Then <OK>.
> 
> Click <ADD>, which allows you to set a second condition and format.
> Click "Formula Is", and enter this formula:
> 
> =AND(I2>0,I2=TODAY())
> 
> Then click on "Format", and choose your font and pattern colors.
> Then <OK>.
> 
> Click <ADD> again for your final condition and format.
> Click "Formula Is", and enter this formula:
> 
> =AND(I2>0,I2>TODAY())
> 
> Then click on "Format", and choose your last font and pattern colors.
> Then <OK> <OK>.
> 
> -- 
> HTH,
> 
> RD
> 
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Phil" <Phil@discussions.microsoft.com> wrote in message
> news:3AA0ADB3-7EDD-44EE-BAFB-BB95F6D26DA3@microsoft.com...
> > OK...I'm a rookie at this kind of thing......I'm missing something
> here....
> >
> > Column "I" has a heading of "DATE DUE BACK" -- I would like to have I2
> thru
> > I90 to turn RED 7 days before the date in that cell. I keep looking/typing
> > the formula in and trying differnet "minus # of days" to see if it
> > changes.....mmmmm........confusion on my end - IDEAS???
> >
> > Thanks much!!!!
> >
> > Phil
> >
> > "Bob Phillips" wrote:
> >
> > > Use a formula of
> > >
> > > =AND(A1<>"",A1>TODAY()-7,A1<TODAY()+1)
> > >
> > > to avoid blanks and future dates
> > >
> > >
> > > -- 
> > >  HTH
> > >
> > > Bob Phillips
> > >
> > > "CyberTaz" <CyberTaz@discussions.microsoft.com> wrote in message
> > > news:EC9E23AF-D170-438C-8BA7-95906E563BEC@microsoft.com...
> > > > Hi Phil-
> > > >
> > > > One Option, there are others- In Format>Conditional Formatting:
> > > >
> > > > Cell Value is         Less than or equal to         =NOW()-7
> > > >
> > > > Set your cell format specs to how you want the date to display.
> > > >
> > > > HTH |:>)
> > > >
> > > >
> > > > "Phil" wrote:
> > > >
> > > > > I maintain a "DEMO" spreadsheet that has a return date for the
> product
> > > of
> > > > > mm/dd/yyyy. I would like to have that highlighted 7 days before the
> > > product
> > > > > is due back. Any help with a formula would be greatly appreciated!!
> > > Also, is
> > > > > there an ability to format so it also emails me a notice?
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Phil Hamm
> > >
> > >
> > >
> 
> 
0
Phil1 (164)
7/24/2005 8:18:02 PM
Reply:

Similar Artilces:

CRM 3.0 Implementation
I am interested in the experiences of others with implementing Microsoft CRM 3.0. I am a one man development team who has been tasked with implementing CRM 3.0 with 30 users initially. Our organization has been running on Lotus Notes for quite a while. We moved to echange for e-mail over a year ago but still use Lotus for custom databases. The first step will be pulling the data from Lotus Notes to CRM. I have looked into the Microsoft CRM 3.0 Certification. There is a company that offers a 10 day CRM 3.0 boot camp. Is this a good idea, and at what point should I take it? We would lik...

Oldest date for Duplicate Cust. #
I'm trying to get the oldest date associated with a customer number, and in the Cust# column, i'll have many duplications of the same customer number. Let's say A is "Date", and B is "Cust#". (I won't be able to allow my users to sort the data, so i'll need a formula that returns either the oldest date, or the cell which contains the oldest date.) Any help is much appreciated! Nevermind. I found it using Google/Groups. {=MIN(IF($B$1:$B$10=B1,$A$1:$A$10))} >-----Original Message----- >I'm trying to get the oldest date associated with a...

crm 3.0 error 03-01-06
Hello, I'me getting this error while installing crm3.0 for SBS: "error writing to file microsoft.mshtml.dll verify that you have access to that directory" That file is in the C:\Program Files\Microsoft.NET\Primary Interop Assemblies directory. I (and 'everyone') has full access to that dir. What can I do about this?? kind regards, Thomas ...

Dates #9
The problem of a date code... I need to address this so that fo example, 5/6/04 can be correctly entered as either 5th of June or 6t of May, depending from where the date emanted. regards -- Message posted from http://www.ExcelForum.com Couldn't you format the cell as mmmm dd, yyyy so that the user sees what date they entered in a non-ambiguous manner right away? Or maybe provide 3 inputs: Month, day, and year. You could combine them elsewhere. "adn4n <" wrote: > > The problem of a date code... I need to address this so that for > example, 5/6/04 can be c...

Office 2003 Service Pack 3--subsequent problems opening Publisher
I run Publisher 2003 on Windows XP. On June 13, I updated my system with Office 2003 Service Pack 3 so that I could open Word documents with the file ext docx. Subsequent to the Service Pack 3 installation, whenever I open a Publisher file (which I created), I get the following message: "Publisher has detected a problem in the file you are trying to open. If you are certain that this file came from a trusted source and does not contain harmful information, click OK." What is causing this and is there a way to stop this pop-up message? All publications? Error message when you...

Qrp Date functions
Where do I find the various functions to modify the Reports like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others and what they mean???? Barry Found the information at MSDN Transact-SQL Reference Barry "Barry L" <barryl@eryanjewelers.com> wrote in message news:usd3uP1CIHA.1188@TK2MSFTNGP04.phx.gbl... > Where do I find the various functions to modify the Reports > like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others > and what they mean???? > > Barry > ...

Fractions Number format
Is it possible to do a custom number format for cells such that fractions are displayed. I'm guessing not because there'd be a limit on what fractions you could display, but I would probably only be interested in showing a half as the little 1/2 (alt,0189 I think) rather than .5 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.478 / Virus Database: 275 - Release Date: 06/05/03 Lee There are a limited number of fractions that can be shown under Format>Cells>Fractions. Gord Dibben Excel MVP - XL97 SR2 & ...

CRM 3.0 Login Problems
Some specific users are constantly getting prompted for CRM login in Outlook. We are using desktop client (online) online. This happens randomly. We have CRM 3.0 with rollup update 2 and IE7. We have also applied this fix http://support.microsoft.com/default.aspx/kb/934243/en-us. Also added the crm site to local intranet zone. Please help. Thanks. set the authentication in IE check rollup update 2 -- Regards, Imran MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "raj" wrote: >...

Showing 3:54PM instead of 3:54:03PM
How do you get rid of the seconds in the time area. I have changed the formatting in the time. I use the excel file as a data source. I include the time in the mail merge. It always shows up with the seconds in the time. Very frustrating. HELP PLEASE!! TJ it may be formatted as text, so won't respond to changing the time format. if it is text, the TIMEVALUE formula will convert it to a decimal-based time value which can then be formatted by using Excel's normal Number formatting-- to get rid of the seconds. Eddie O "TJ" wrote: > How do you get rid of the secon...

Hyperlink problem #3
I've got two workbooks on a shared drive with hyperlinks linking the two. When a user clicks on the hyperlink on the first workbook, it takes him to the second workbook. Fine. However, when the user clicks on the hyperlink in the second workbook to go back to the first, the error message says that that workbook is already open and it cannot open two files with the same name. Help is appreciated! I just tried a small test in xl2002 and it worked ok for me. I use Insert|Hyperlink to create the links. Are you sure that the hyperlinks point at the file you want--same folder and e...

date tracking
I am entering clients into a 2007 Excel spreadsheet. How do I make the entry turn color when 14 days have passed? Gene This is a multi-part message in MIME format. ------=_NextPart_000_0018_01CAC8D4.5688AC60 Content-Type: text/plain; format=flowed; charset="UTF-8"; reply-type=original Content-Transfer-Encoding: 7bit As part of the "client" entries, do you enter the date the client was entered? This would be the key in doing this task. In a cell on the worksheet you could enter the formula for today's date like this =TODAY(). Then use the con...

Outlook Client #3
Dear All, I have recently installed crm outlook client for one of my users and then also installed the 2 rollups for version 3.0 . Unfortunately outlook is still restarting even after the rollups. Kidnly advise the necessary solution that resolve the problem. Please clarify, outlook loads and then crash "restart"? Has Office applied with latest Office update? Frank Lee, Microsoft Dynamics CRM MVP http://microsoft-crm.spaces.live.com http://www.workopia.com/Links.htm "Faiz Amir" wrote: > Dear All, > I have recently installed crm outlook client for one of my ...

Rounding Numbers #3
I have a list of values as below: 476.14 361.99 345.69 463.08 515.29 403.44 330.68 347.64 375.36 I would like to create a formula that rounds the values to the nearest 0.05 eg. Round 476.14 to 476.15, 361.99 to 362.00, 375.36 to 375.35 etc… Is there anyway that I can do this? Thanks, Jane. JaneC wrote: > I have a list of values as below: > 476.14 > 361.99 > 345.69 > 463.08 > 515.29 > 403.44 > 330.68 > 347.64 > 375.36 > I would like to create a formula that rounds the values > to the nearest 0.05 eg. Round 476.14 to 476.15, > 361.99 to 362.00, 375.36...

Short time vs short date
In my form I have a fldOutOfRoom which the user inputs a short time into the field, i.e, 0900. I have the following code in the open event of another form called frmRNnotes: If DateDiff("h", Forms!frmPtDemographicNew!frmVisitNewEdit.Form!OutOfRoom, Now) >= 1 Then Me.cmdRNnotesEdit.Visible = False This code gives the RN one hour to complete a note and then he/she can no longer edit the note. What I want to know is the Short Time format going to let the cmdRNnotesEdit button be visible everyday within one hour of the of the original time? That is, is the short time just a tim...

How to save Japanese characters in CSV format of Excel ?
Hi, I would like to know, how to save the Doubly-Byte characters.For example, Japanese in CSV of Excel.Here are the steps, I tried. 1. Open Excel 2. Copy pasted the Japanese charactesrs in a cell.It is displaying the characters correctly. 3. Save as CSV(Comma seperated file) It is saving as ??????. Thanks in advance for the info. ...

Some Excel formatting functions taking a long time to work #2
Excel 2000 SP3 When I use some formatting functions for the first time in a session, such as bold, increasing font size etc, it takes up to 30 seconds to work. Meantime Excel is locked up until it completes that formatting call. I suspect faulty DLL? Has anyone experienced this? How to fix (other than a complete re- install) ? Any advice appreciated. Piri On Nov 5, 8:39=A0am, Piri <wiremu.pare...@hotmail.com> wrote: > Excel 2000 SP3 > When I use some formatting functions for the first time in a > session, such as bold, increasing font size etc, it takes =A0up to 30 > secon...

SetWindowsHookEx #3
Hi there. Could someone explane to me what is the purpose of dwThreadId, the last member of SetWindowsHookEx function? I've expected that this is a thread id with which hooks is associated. That means that hook is getting only messages produced by this particular thread. But it looks like I'm getting system wide messages anyway. so could somone make it clear for me? In fact I need to process a mesages of only one window. I know the thread id of that window. But! this thread is not in my process! And I don't want a real system wide hook because it slows down the box. Ok, I think ...

RMS 1.2 vs 1.3, plus integrate with Great Plains
1.) I am working on an assessment for upgrading our current RMS software from 1.2 to 1.3. My assessment will take in account the benefits, broken down by store operations (Retail) and the benefits to IT. Also, list risks and potential problems that might be experienced. We currently have 28 stores with 3+ registers per location with another 22 new stores on the schedule over the next 2 years. Are their other in this group of similiar size that have done this upgrade to the new version? 2.) If we do not upgrade the software, can we still integrate the RMS to Great Plains? Would we...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

date modified
I have two sheets Data and Summary The "data" sheets macro extracts data from external file and paste into "Data" sheet Everytime the m acro is run to get latest data... The macro delete all contents of the "data" and then paste new data into the "data" sheet. Is there a way.. I can put a date on the "summary" sheet, when was the time the macro was run ( or in other words.. the data updated) This little macro records the date in the selected cell and formats it: Sub Macro2() Dim d As Date Dim s As String d = Now() s...

Date function quit working
Hi, I have an Access 2002 application that I have been running on Windows XP SP2 without issue. I just installed the application (running in Access Runtime) on a Windows Vista Home Premium machine. Now, anywhere I used the =Date() function, it fails and just shows #Name? I also have a subform on one of my forms that has now gone blank. It also uses the date function. I had this problem when I converted to Windows XP several years ago and updating the OWC10.dll to version 6619 fixed both issues. However, everything I have read says that reference file makes no difference to the Access...

Date Calculation
Good Afternoon, I have a DB which tracks training of employees. The grace periods allowed with the training is that new training can be completed within 90 days of the expiry date without changing the anniversary date (e.g. the training is due on 1 April 2010, the employee conducts the training on 2 January 2010 but gets to keep the 1 April anniversary date). The table I am working with is mainly based on the date of training and the training type (which determines whether the training expires on the 1st of the 13th, 25th or 37th months or if it keeps the same date); what I w...

Date format 04-11-06
Hi, Is there a possibility that the dates used in all the entities are not in the default format mm/dd/yyyy but in dd/mm/yyyy. I already adapted the Organisatonal settings, that only adapts the journal but nog the dates of an appointment. Does anyone have an idea? Thanks, ...

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...

copy values generated by conditional formula in one sheet to the other work sheet as values
Hi Everybody, I have data generated by conditional formulae in work sheet1 in columns A to J. If the condition is satisfied the cell will display a realnumber, if the condition doesn't satisfied the cell will display the text"FALSE". Now I wanted to copy the cells which have the real numbers in sheet1 to sheet2 as values(as we do with paste special and paste the values) Do we have any formula or other method to copy the cells in sheet1 to sheet2. can anybody helpme out in this issue. Thanks and Regards Ramana Select your range to copy edit|goto|special|c...