conditional format a date in Excel

I am trying to use conditional formatting to show when a date is less than 30 
days in the future. When I use "cell value" "is less than" "="Now()+30" all 
the dates change. 
0
copteral (3)
2/17/2006 3:45:27 PM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
445 Views

Similar Articles

[PageSpeed] 53

I am trying to use conditional formatting to show when a date is les
than 30
days in the future. When I use "cell value" "is less than" "="Now()+30
all
the dates change.

lose the quotes in the condition =Now()+30 

Unless you mean between today and 30 days in the future in which cas
=now() would be the first condition and =now()+30 would be the second

Also now() returns the date and time is this what you require ofte
today() which just returns the date is preferable

Regards

Da

--
Da
-----------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2710
View this thread: http://www.excelforum.com/showthread.php?threadid=51369

0
2/17/2006 4:59:57 PM
Thanks for the quick response, actually, the Excel (computer) puts the quotes 
in. I did finally find a Discussion from 2004 that helped also by using 
Today() instead of Now() and got it to work.

Al

"Dav" wrote:

> 
> I am trying to use conditional formatting to show when a date is less
> than 30
> days in the future. When I use "cell value" "is less than" "="Now()+30"
> all
> the dates change.
> 
> lose the quotes in the condition =Now()+30 
> 
> Unless you mean between today and 30 days in the future in which case
> =now() would be the first condition and =now()+30 would be the second
> 
> Also now() returns the date and time is this what you require often
> today() which just returns the date is preferable
> 
> Regards
> 
> Dav
> 
> 
> -- 
> Dav
> ------------------------------------------------------------------------
> Dav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27107
> View this thread: http://www.excelforum.com/showthread.php?threadid=513696
> 
> 
0
copteral (3)
2/17/2006 5:36:30 PM
If this is still open:

Your date in B1:

select format-->conditional format
select "formula is"
type =B1<today()+30
click "format"
select the formatting you want
click ok
click ok
copy B1 and "paste special-->format"  into all cells that need
formatting.


Is that what you wanted?

Hans

0
hansyt (53)
2/17/2006 5:38:19 PM
Yes, that is exactly what I wanted.

Al

"flummi" wrote:

> If this is still open:
> 
> Your date in B1:
> 
> select format-->conditional format
> select "formula is"
> type =B1<today()+30
> click "format"
> select the formatting you want
> click ok
> click ok
> copy B1 and "paste special-->format"  into all cells that need
> formatting.
> 
> 
> Is that what you wanted?
> 
> Hans
> 
> 
0
copteral (3)
2/17/2006 10:08:27 PM
Reply:

Similar Artilces:

exit excel within vba
Hello All, I have a vba code, at the end of this code I want to exit excel not close. If I close the excel workbook I can not run excel again from the scheduler. Can anyone provide me help?? thanx all. Murat Demir Hekimoglu You want to quit excel? Application.quit could be what you're looking for. Murat Demir HEKIMOGLU wrote: > > Hello All, > > I have a vba code, at the end of this code I want to exit excel not close. > > If I close the excel workbook I can not run excel again from the scheduler. > > Can anyone provide me help?? > > thanx all....

Excel Bar Graph Questions
I want to compare FY08 & FY09 data (By each mth) using excel bar graph. So, on the X-axis need to show 08 & 09 bar graph in DIFFERENT color. What's the restrictions now I can see is ONLY ONE color shown on data on X axis. Anyone knows how to do it? Many Thanks Hi, Did you move one of the series to the secondary axis? If so see http://peltiertech.com/Excel/Charts/ColumnsOnTwoAxes.html Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Ruby" <Ruby@discussions.microsoft.com> wrote in message news:223C6B4E-345D-40B7-A217-AEA3FA686F...

Can Excel 2007 link with Outlook 2007
I have a list of companies names that I need to add the addresses to. I don't want them to be on the same worksheet. I will need to add compaines to the list from time to time. Also Is it possible to link these addresses with outlook? Thanks, Dan ...

Excel text box #2
Is there a way to set the default text alignment to "center" for both vertical and horizontal in the draw text box? I am using Excel 2000. I found it for the cell default. That's what I want to do with the draw text box. -- Thanks To get the horizontal centering--just select your text and hit the Center Icon on the format toolbar--just like you would to center text in a cell. I had to hit enter a few times (at the beginning of the text) to center it vertically. rvrl wrote: > > Is there a way to set the default text alignment to "center" for both > ...

Sport Times Formats (elapsed time)
Does anyone have the definitive word on how to handle elapsed time? I need to track and manipulate swimtimes in Excel. Excel gets SO confused, and converts the times into various items. I can't believe that after 10yrs of using Excel, this ability is either missing or so hard to use. Please enlighten me! Thanks, Tom M. Tom, As you refer to swimtimes, I assume you want 1/100th of a sec. All you need to do is format the cells, including the result cell, as hh:mm:ss.00 and just subtract the end time from the start time. As Excel stores time as a fraction of 1 day, it is straightforw...

Conditional Format "OR"
I just discovered the answer to a problem and thought that I would share it with the group. How do I create a conditional format such that: a cell must be between 1 and 100 or greater than 1000? Answer: =OR(AND(A1>0,A1<101),A1>1000) I hope this helps some else. Lee ...

Excel missing file
I am a registered user of Office XP Pro. Excel will not start. It is missing a file. In the process of moving, disks in storage. Where can I go online to download the missing file? And, how? ...

Excel integration of models in various workbooks
I have a few different models in seperate excel workbooks ... one for cars, one for houses, one for retirement accounts.. I recently had the idea to take outputs from each of these models and plug them into a single workbook something like life.xls. What I'm wondering is if there is a way to wrap them (the house and car models) with an interface (VBA?) which I can pass parameters and can call from a macro in life.xls to retrieve the output from. Essentially I'd like each of the workbooks (house,..cars) to be like a callable function into which I can pass parameters, and which return...

How can i change chart data point format based on value?
I have a simple column chart with a single data series. I would like to change the color of an individual data point column depending on value. Thanks very much. You want to make a conditional chart: http://peltiertech.com/Excel/Charts/ConditionalChart1.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Harold96" <Harold96@discussions.microsoft.com> wrote in message news:3674450D-70A9-4227-B29A-36262B7BE723@microsoft.com... >I have a simple column chart with a single data series. I would...

Can I stop formatting changes from appearing in "track changes"?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Under track changes preferences, I have &quot;none&quot; selected, yet formatting changes still show up when I chose to display the changes in balloons. I can accept most of the formatting changes in order to eliminate the balloons, but sometimes I click to &quot;accept&quot; 10 or 20 times, but the balloon won't go away. <br><br>I realize that this is only a problem with balloons, but my pagination gets messed up if I don't use balloons. Yes: Turn off "Keep track of f...

Active X Controls Shrinking on Excel worksheet
I have a workbook and sometimes the ActiveX controls such as listbox, options, checkboxes on the worksheet shrinks or expands while clicking on the controls. Does somebody has experirenced same kind of problem or somebody knows the fix. The only to fix this problem is to restart the machine again. Thanks in advance. ...

excel #13
I need to know if there is a way to put a scroll bar with text, as a pull down menu in a cell. Hi Chelo, Check out Data | Validation | List. HTH -- Regards, Zack Barresse, aka firefytr "Chelo" <Chelo@discussions.microsoft.com> wrote in message news:8BB85A00-4175-47C7-A4EF-52260FD5049A@microsoft.com... >I need to know if there is a way to put a scroll bar with text, as a pull > down menu in a cell. ...

Please help me with my Excel
I have got a new lap top and I have a good antivirus and spyware. my pc works good but once I open Excel my CPU jumps up to %100 and the strange thing is that when I click on other programs my CPU comes back to %4. The more strange thing is that when for example I press F2 to edit a cell , CPU works good but when I press Enter, again it goes back to %100. Here our IT man reinstalled my MS-Office and he did some other exotic operations but there was not any slightest change and the problem is still existing and going on my nerve. Please scientists help me ! I work with Excel 7 hrs per day ...

non linear regression in excel
Hello I have some charts which I am trying to fit lines of best fits to. The general shape of these curves is something representing '4 o' clock' on a clock. I am trying to do non-linear regression to get the closest fit to these curves but the trendline functions on excel are very limited (ie logarthmic,exponential,polynomial etc) and do not produce a sufficient trendline. I was wondering if anyone knows how I could possibly produce the trendline that I require that fits the data on the curve. Many thanks for your help. Warm regards Nikeel - If the general shape is a singl...

GP BOM should be able to select variable BOM from date or range
I had a request from prospect to see if GP could allow their Mfg Mgrs to create a cutoff or date range from which to select different BOMs. For example: They wish to change ingredients used in a dietary supplement starting Tuesday. Work orders from Tues onward would use an alternate BOM. All work orders prior to Tuesday would used the original BOM. There are other similar scenarios. Thanks ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the...

Excel 2002: Can I save all files by just one click ?
Hi, I have 10 working files in an active window. There is no Save All button under the file menu. May I know if I can save all the files at one click without having to exit excel ? Thanks Low This delivers it in 2 clicks ... Hold down Shift key, click File > Close All Then click "Yes to All" in the ensuing prompt to save changes Success? Celebrate it, hit the YES below -- Max Singapore --- "Mr. Low" wrote: > I have 10 working files in an active window. > There is no Save All button under the file menu. > May I know if I c...

Changing format of a particular point in a series (conditionally/vba)
I have a single series which is charted each month and want to automatically format one of the points (a bar) a different colour. Is it possible to do this automatically? thanks for any advice david marcus - note the email address will not work Hi! Click once on the particular bar where you wish to have different color, system will select all bars in the chart. Click once again, the system will select only the bar that you are clicking on. Call out Format Data Point and select the desired color or pattern. >-----Original Message----- >I have a single series which is charted eac...

Dynamic Excel
Dear All Is there anything that I can do in Excel to my Excel report dynamic? For eg... is it possible to create something which will create results based on figures keyed in... ? Any suggestions would be welcomed... It would be good if u could send me similar Excel project of yours... to me as reference... Cheers --- Message posted from http://www.ExcelForum.com/ Hi this is a little bit vague :-) maybe as a starting point: http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/exce...

How do I remove the (blank) from an excel pivot table?
I am trying to create a pivot table from a liston an excel spreadsheet. Many of the items on the list will be blank. On the pivot table, the blank items show up as (blank) - I want the cell to be empty! I tried setting the table empty values but that doesn't work...any help would be greatly apprecated... If you click the small black triangle near the top of the Table, a menu will appear. Just clear the checkbox next to (blank) -- Gary''s Student "MarkfromAZ" wrote: > I am trying to create a pivot table from a liston an excel spreadsheet. Many > of the...

Refreshing a Microsoft Office Excel Chart Object
Can a Microsoft Office Excel Chart Object be refreshed by linking it to an excel spreadsheet? The "chart object" is being copied from the originating excel spreadsheet, using the special past function to make an Excel Chart Object in a Word doc. DonC, Use Paste Special, pasting it as an Excel Chart Object in the Word document. Use the Paste Link button. Now it's linked to the cells of the original worksheet. -- Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- "DonC" <don.cook@farmersinsurance.com> w...

Excel 2002 #5
Is there a way to set an excel file to open to the same worksheet regardless to where it was last saved? Trip, In the ThisWorkbook code module, put the following code. Change "Sheet1" to the desired worksheet name. Private Sub Workbook_Open() Worksheets("Sheet1").Select End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Trip Levert" <anonymous@discussions.microsoft.com> wrote in message news:095401c3bdaa$69673f90$a401280a@phx.gbl... > Is there a way to set an excel file to open to the s...

How to convert an excel macro code to a Visual basic program
As it is in the subjec title, this is my question: How to convert an Excel macro code to a Visual Basic program? The fact is I am using some excel macros written for excel 2002 and I want to work with them just like a distinct program. I tried to do it directly, but I faced with lots of errors. Any help would be highly appreciated. ...

If I closed an excel doc without saving can I get it back? thx
I opened up 2 version of excel and tried to close one, I forgot the other was opened and when I chose not to save it I lost my old one also. If there anyway I can get it back? I tried system restore but that was turned off also ;( Thx for any help! If that file was originally out on the drive, then you should be able to just open it up and have the pre-changes-made version of it available. However, if you were creating it from scratch and closed without saving it, then it is gone. System Restore would never help in a case like this - System Restore does not save user data; it is a...

if Condition #2
Can anyone clarify my doubt please. I have 3 fields condition 1, condition 2 and condition 3. If the value of condition 1 or Condition 3 is equal to 1 then my result should show as 10. How can do this? Can I use this using If condition, then how can I do it? Maybe??? =if(or(a1=1,c1=1),10,"what should go here") saraskandan@gmail.com wrote: > > Can anyone clarify my doubt please. I have 3 fields condition 1, > condition 2 and condition 3. If the value of condition 1 or Condition 3 > is equal to 1 then my result should show as 10. How can do this? Can I > use this ...

Manually Uninstalling Excel Viewer
HELP! I need to manually uninstall my corrupt Excel viewer. Would somebody offer some details as to this task? Thank you. I've never installed nor uninstalled Excel Viewer, but the standard procedure for uninstalling a program is to: Close Excel Viewer Click Start Select Control Panel Find and double-click on "Add or Remove..." Find Excel Viewer in the list of programs. Click on Remove. Follow screen instructions. HTH Otto "Steve Evans" <sjevans2@ameritech.net> wrote in message news:uXSilFs3DHA.1924@TK2MSFTNGP10.phx.gbl... > HELP! > > I need to ma...