Today Conditional Format

I have a column which calculated a date based on 3 working days from a date 
input into a diff column.  What I want to do I have the calculated date 
appear bold if that date has passed based on the current date.

For example if I input 01/08/11 the next column calculates 04/08/11 (3 
working days).  I want the caluclated date to appear bold when I open the 
spreadsheet on the 05/08/11 ro show the calculated date has passed.

I can use conditional formatting but can't get the syntax right

Regards

Andy
Win XP Pro
Office 2010 


0
andy9512 (17)
8/2/2011 7:40:37 AM
excel 39879 articles. 2 followers. Follow

6 Replies
1216 Views

Similar Articles

[PageSpeed] 8

Andy Roberts presented the following explanation :
> I have a column which calculated a date based on 3 working days from a date 
> input into a diff column.  What I want to do I have the calculated date 
> appear bold if that date has passed based on the current date.
>
> For example if I input 01/08/11 the next column calculates 04/08/11 (3 
> working days).  I want the caluclated date to appear bold when I open the 
> spreadsheet on the 05/08/11 ro show the calculated date has passed.
>
> I can use conditional formatting but can't get the syntax right
>
> Regards
>
> Andy
> Win XP Pro
> Office 2010

In the CF dialog choose 'Cell Value is Less than' and type  
'="today()"' into the ref edit box.

-- 
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


0
gs3102 (378)
8/2/2011 5:41:53 PM
[Off topic to this thread]

Garry,

I know the "How to copy non-contiguous columns to a text file" thread is 
pretty much dead, so I'm thinking you are not checking it anymore, but I 
came up with an idea for a solution and posted some code to it that you (and 
the OP who I hope comes back to it) might find interesting.

Rick Rothstein (MVP - Excel)



"GS"  wrote in message news:j19ct8$98r$1@dont-email.me...

Andy Roberts presented the following explanation :
> I have a column which calculated a date based on 3 working days from a 
> date input into a diff column.  What I want to do I have the calculated 
> date appear bold if that date has passed based on the current date.
>
> For example if I input 01/08/11 the next column calculates 04/08/11 (3 
> working days).  I want the caluclated date to appear bold when I open the 
> spreadsheet on the 05/08/11 ro show the calculated date has passed.
>
> I can use conditional formatting but can't get the syntax right
>
> Regards
>
> Andy
> Win XP Pro
> Office 2010

In the CF dialog choose 'Cell Value is Less than' and type
'="today()"' into the ref edit box.

-- 
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

0
8/2/2011 6:27:53 PM
Rick Rothstein used his keyboard to write :
> Garry,
>
> I know the "How to copy non-contiguous columns to a text file" thread is 
> pretty much dead, so I'm thinking you are not checking it anymore, but I came 
> up with an idea for a solution and posted some code to it that you (and the 
> OP who I hope comes back to it) might find interesting.

Rick,
I did see your post there and found it to be very interesting, indeed. 
I wanted to play with it for a bit before giving you my feedback. 
Though it does not address the OP's needs "as is", it looks like a good 
candidate for assembling non-contiguous data.

-- 
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


0
gs3102 (378)
8/2/2011 10:26:14 PM
> ...Though it does not address the OP's needs "as is"...

We should actually carry this conversation on over in the other thread, so 
don't respond to this question until you are ready to respond over there; 
however, I'm guessing the OP must have changed her requirements somewhere in 
that long and winding thread then, I went with this from her opening 
message...

"I would like to copy columns just B and J to a tab-delimited text file."

I think the file my code outputs would be considered a tab-delimited text 
file, wouldn't it?

Rick Rothstein (MVP - Excel) 

0
8/2/2011 11:42:08 PM
Thnaks Garry

I had tried that but the conditional formatting gets applied whether the 
date is before or after today's date. In other words all cells change colour 
irrespective of the date.

Do you think this may have something to do with the date format?  I am using 
dd/mm/yy

Andy

"GS" <gs@somewhere.net> wrote in message news:j19ct8$98r$1@dont-email.me...
> Andy Roberts presented the following explanation :
>> I have a column which calculated a date based on 3 working days from a 
>> date input into a diff column.  What I want to do I have the calculated 
>> date appear bold if that date has passed based on the current date.
>>
>> For example if I input 01/08/11 the next column calculates 04/08/11 (3 
>> working days).  I want the caluclated date to appear bold when I open the 
>> spreadsheet on the 05/08/11 ro show the calculated date has passed.
>>
>> I can use conditional formatting but can't get the syntax right
>>
>> Regards
>>
>> Andy
>> Win XP Pro
>> Office 2010
>
> In the CF dialog choose 'Cell Value is Less than' and type  '="today()"' 
> into the ref edit box.
>
> -- 
> Garry
>
> Free usenet access at http://www.eternal-september.org
> ClassicVB Users Regroup! comp.lang.basic.visual.misc
>
> 


0
andy9512 (17)
8/3/2011 9:00:40 AM
on 8/3/2011, Andy Roberts supposed :
> Thnaks Garry
>
> I had tried that but the conditional formatting gets applied whether the date 
> is before or after today's date. In other words all cells change colour 
> irrespective of the date.
>
> Do you think this may have something to do with the date format?  I am using 
> dd/mm/yy
>
> Andy
>
> "GS" <gs@somewhere.net> wrote in message news:j19ct8$98r$1@dont-email.me...
>> Andy Roberts presented the following explanation :
>>> I have a column which calculated a date based on 3 working days from a 
>>> date input into a diff column.  What I want to do I have the calculated 
>>> date appear bold if that date has passed based on the current date.
>>>
>>> For example if I input 01/08/11 the next column calculates 04/08/11 (3 
>>> working days).  I want the caluclated date to appear bold when I open the 
>>> spreadsheet on the 05/08/11 ro show the calculated date has passed.
>>>
>>> I can use conditional formatting but can't get the syntax right
>>>
>>> Regards
>>>
>>> Andy
>>> Win XP Pro
>>> Office 2010
>>
>> In the CF dialog choose 'Cell Value is Less than' and type  '="today()"' 
>> into the ref edit box.
>>
>> -- Garry
>>
>> Free usenet access at http://www.eternal-september.org
>> ClassicVB Users Regroup! comp.lang.basic.visual.misc
>>
>> 

It probably has more to do with the number of format conditions you 
have AND what order they're in. Otherwise, being the only CF condition, 
I found it to work exactly as you expected it to.

-- 
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


0
gs3102 (378)
8/3/2011 5:02:45 PM
Reply:

Similar Artilces:

VBA code for copying part of a sheet to another, with column widths and formats
Hello, I'm trying to take a range from one sheet(Range.cells(1,1),cells(10,10)) and copy it from sheet 1 to sheet 2. The copy is easy. But what I don't understand how to do is to carry over with the copy all of the column widths and numeric formats. Can someone please explain how this is done in VBA code? thanks The column widths are a seperate Paste Special operation. Something like this will work Sub test() Sheets("Sheet1").Activate Range(Cells(1, 1), Cells(10, 10)).Copy Sheets("Sheet2").Activate Range(Cells(1, 1), Cells(10, 10)).Select Sel...

Money date formats
How do you apply a change to the date format for MS Money Have you tried setting the date format on the regions control panel? Many Money formats come from there. "michael furniss" <anonymous@discussions.microsoft.com> wrote in message news:055601c39837$711bb3d0$a401280a@phx.gbl... > How do you apply a change to the date format for MS Money ...

Modifying chart-Data and formatting
Noob here so I'll try not to test your patience. I've been handed some existing word doc's to modify. I'm adding some VBA programming to speed up data entry of a Test Data Record for one of our products. The doc has one embedded chart, which up to this point, the technician has had to open to gain access to the underlying data (excel spreadsheet). In the end, I'd like this doc to be a form, saved as a template. When the technician opens the template, he selects from a pull down menu (field), which upon exit, the chart is modified, preparing for when the techn...

how come =date(year(today()),month(today())+6,today()) show 2097?
While evaluating, everything goes fine till it reaches today(), as 39216 and suddenly the result is the one given above. Why? Sorry, got the error. "dindigul" <padhye.m@gmail.com> wrote in message news:esGfEAalHHA.492@TK2MSFTNGP04.phx.gbl... > While evaluating, everything goes fine till it reaches today(), as 39216 > and suddenly the result is the one given above. Why? > Maybe you meant: =date(year(today()),month(today())+6,day(today())) I wasn't sure if you found a solution or not. dindigul wrote: > > Sorry, got the error. > "dindigul&...

Conditional ComboBox
I have a combo box that allows users to choose a staffID, however if a certain item is selected in a previous combo box then my list of staffID's needs to be limited to a certain few. Currently this is what I have. If 27 < Service.Value < 40 Then Me.STAFFID.DropDown="24","159","176","321","326","337","383","124","390", "409" End If The certain item is the service. So if the service.value is between the range then the staffID's should be limited to only certain ID's otherwise...

View Only Today's Tasks
OL 2003, XP Home When viewing the calendar and the task pad at the same time, I want to see today's tasks and past due tasks, but when I go to View > Taks Pad View > Today's Tasks I still get tasks for all dates (but no tasks without due dates). How can I fix the filters that it seems I messed up? -- ICQ# I also can't get it to display tasks for Active Tasks for Selected Days. It keeps showing the same tasks as Today's Tasks! Filter Hell ahhhhh! -- ICQ# "Diggy" <jamesOU812@hotmail.com> wrote in message news:%23pLxtWasDHA.2464@TK2MSFTNGP12.phx.gbl...

Conditional Formatting #117
I have some conditional formatting in column J on my worksheet that checks if it is less than the value in column C. If so, the font color is red. However, column C can also be blank sometimes and if I put a value in column J, then it displays it in red font color, which I don't want. Is there a way to add something to the conditional formatting which will only display column J in red font if it is less than column C and column C is not blank? -- LAF ------------------------------------------------------------------------ LAF's Profile: http://www.excelforum.com/member.php?actio...

Making my own data file (pst) the outlook today file
I have just setup Outlook 2003. I have opened my old PST file. I wish to use this and make this my default data file. I will therefore not require the Personal Folder which is the Outlook today default I think I need to make my opened PST file the default file before I can delete the Personal Folders file But how do I do this...... Thanks Go to Tools | E-mail Accounts, select View or change existing e-mail accounts, click Next, and then choose your preferred file in the "Deliver new e-mail to the following location" dropdown box. Click Finish, and then you will be able to close...

Outlook Today
I have two problems in Outlook Today. Here's the scenario. Today is Saturday 3rd Jan, 2004. In Customize Outlook Today I have "Show this number of days in my calendar as 7 " In Outlook Today under the Calendar column I have a multi-day event correct displayed under today. However, even though this multi-day event ends on Tuesday 6th Jan I cannot see any further listings for it in the Calendar Column. Should I not see it listed for Sunday and Monday and Tuesday as well? The second problem is I have an appointment penned in for Sunday 4th, 2004. (i.e. tomorrow) yet this is now...

How do I save my Customized Outlook Today when it will not save b.
I am trying to set my Outlook Today to the Winter settings. I will go into Customize Outlook Today, make the changes I want, then I will click "Save Changes." What I want it displayed, untill I go to...say my inbox. When I go be to Outlook Today, it displayes the default settings again. How can I fix this? ...

the dates on cell format make different dates.
Please, I'm just getting farther from the answer and I have to be up for church soon. First question. I'm obtuse but when I type in a date, eg jan 1 05 and then format/cell/date I select a date format and it morphs in to a completely different date. 2nd question: to change case, use UPPER or LOWER function. But, how does that work to use the cell itself or a group of cells. If anyone can help, I'll owe you forever. carrie, washington state carrieaa@hotmail.com 1) What does the date morph into? 2) Take a look at David McRitchie's macros: http://www.mvps.org...

today
Hi I was wondering if there's a way to add today's date statically (so it won't auto update) to a cell inside a formula. I would like the cell to enter today's date automatically when a value is placed in cell A:12, something like this; =IF(NOT(A12=0),TODAY()," ") This works except this will update the date each time I open the workbook and I don't want that. Thanks. You could change the formula to something like: =IF(A12=0,"",DATE(2007,11,15)) for today's date. I've made a few other changes - got rid of NOT and reversed the order, a...

EXPERT HELP REQUIRED
Can any one offer any assistance with the following query. See example table in a worksheet; Column A B C D E F Seq Month C/C A/C Ac name Actual � 1 Jun 04 7313 30613 Training(NR) �2,250 2 Jun 04 7323 60301 Plant Hire �5,500 3 Jul 04 7324 30810 Prot clthng �1,500 4 Aug 04 7313 30612 Training (R) �6,500 ETC.... NOTES: -The data at the moment extends to 600 line items for one mont therefore potentially could be circa 7,500 line items -I dont want to use pivot tables as this is to be template to issue to other users with little or no...

Too many cell formats #2
I have a set of spreadsheets that I am unable to change the format of any cells. When I try to change the background color, font or font size, an error message pops up saying there are too many cell formats. I really want to avoid having to re-create all of these spreadsheets. Please help. Thank you. Excel has a built in limit to the number of cell formats. You have reached that limit. As the message indicates, you cannot use any additional cell formats. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "wsdanalyst" <...

OpenForm
On a main form giving "Organisation" details I have a subform showing brief details of persons associated with that organisation in continuous forms format. By clicking on the record selector of a record in this sub form I want to open a form which gives complete information about the relationship between the organisation and the person. I am using the following code to do this Private Sub Form_DblClick(Cancel As Integer) DoCmd.OpenForm "frmEditPerOrg", , , "PerOrgID = " & Me!PerOrgID End Sub However frmEditPerOrg opens with a blank new record, not the o...

Workflow with a waiting condition
Hi! Is it possible to create a workflow that starts when a quote hasn´t been converted to an order for about 7 days? The workflow should assign the quote to a queue so a special apartment can follow up the quote. Thanks! ...

Conditional Formatting in 2007
Playing with Excel I'm trying to conditionally format cells that divided evenly in a range, heres a simple example :) In cell A1 I put '=50000000/row()' I then extend (drag) that for 100 rows. What I'd like to do is then select A1:A100 and conditionally format so the ones that divded with no remainder are highlighted. I can't figure out how to format a whole range with a formula. You do not need your formula in A1 and down. Just select A1:A100, goto Format > Conditional Formatting > Formula is: '=Mod(Row(),2)=0' Select formatting as desire...

Find (Today-21) in a range of dates
I need to write a formula which will search a range of cells containing dates and if any of those is greater than todays date less 21 days, then return the text "not due". This is for a customer data base which has home page showing all customers, and subsequent pages for indvidual customers. As contact dates are added for each customer on their own sheets, then the home page should show if they are due (or overdue) for a call =IF(MAX(rng)>(TODAY()-21),"not due","due") -- HTH RP (remove nothere from the email address if mailing direct) "JG&quo...

Incorrect formatting in email
In Outlook 2003 my email format defaults to PARAGRAPH when starting a new email. Everytime I begin I must first change it to NORMAL. When replying to an email, however, the format default is NORMAL. How can I have the default NORMAL in both cases Thanks Richar ...

"conditional formatting" "#values"
I have a formula that works fine but... some of the cells show #values, what i would like to do is conditional formatting them to white font if = #values. Any pointers? many thanks Try this: Select the cells to be impacted (I'll assume A1:A10, with A1 as the active cell) From the Excel main menu: <format><conditional formatting> Condition_1 Formula is: =ISERROR(A1) Click the [Formatting] button.....set the White font....Click the [OK] buttons. Does that help? *********** Regards, Ron XL2002, WinXP "Dewi..." wrote: > I have a formula that works fine bu...

Conditional format of values
I have a list of lap times and need to be able to colour the cells with the fastest and slowest lap times respectively. Is there a way in excel I can format the cells beforehand so that they update when the times are entered? Thanks for any info Say the lap times are recorded in a range called MyRange and a cell MyCell is within MyRange Then the conditional formatting of MyCell might be set to for maximum (condition 1) FormulaIs: =MyCell=MAX(MyRange) for minimum (condition 2) FormulaIs: =MyCell=MIN(MyRange) If the maximum coincides with the minimum it would be treated as maximum in the ab...

POCKET PC 2003 EXCEL TO STOP CHANGING THE formating
everytime i sync, pocket pc changes the formating of cells, is there any way to stop this. ...

Outlook Today
I have duplicate "Outlook Today - [Personal Folders]" on my folder list. They seem to be exact duplications. When they are expanded they have the same subfolders and changes to one are reflected in the other. Is there some way to get rid of the duplicate folder? Also, below the second "Outlook Today - [Personal Folders]" is an additional folder called "Personal Folders" in the same vertical hierarchy as the two "Outlook Today - [Personal Folders]". When I click on the "Personal Folders" folder I get a message that says "! Unable ...

Task lost formating
We use Word to create a table to bring over to a task. Suddenly, this past week, many of our tables are gone although the text is still there. ...

Formatting Percentages Redux
Ages ago I asked a question about formatting percentages (specifically in TextBox controls on a UserForm) to display a plus or minus sign as appropriate - something that the 'FormatPercent' function doesn't support. I received a most helpful answer from either Jay or Doug - I've slept a few times since then and can't remember exactly who it was - describing a method for using the basic 'Format' function to meet my needs, and things have been working a treat ever since. Until now... Here's the situation. I need a method whereby percentage values...