today() conditional formatting

i have conditional formatting set for projects that are still pending so that

formula is =today(
formatted font is red, bol

once the project is complete, the date is manually entered into the cell (changing it from the formula =today() to an actual date (i.e.: 4/19/04))

the problem is that the conditional formatting at that point still reads the date in that cell as today's date (even though it's no longer a formula), and continues to show as red, bold font... which indicates that the project is still pending

is there a way to get around that

hope that makes sense

tia
jill


0
anonymous (74721)
4/19/2004 9:11:02 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
439 Views

Similar Articles

[PageSpeed] 32

You need to include a reference to the cell itself.

Select all of the relevant cells, say starting at B2, and change the formula
to

=B2=TODAY()


-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jill" <anonymous@discussions.microsoft.com> wrote in message
news:08A7D1E9-ACF2-475C-A6D7-8FEE898706E8@microsoft.com...
> i have conditional formatting set for projects that are still pending so
that
>
> formula is =today()
> formatted font is red, bold
>
> once the project is complete, the date is manually entered into the cell
(changing it from the formula =today() to an actual date (i.e.: 4/19/04)).
>
> the problem is that the conditional formatting at that point still reads
the date in that cell as today's date (even though it's no longer a
formula), and continues to show as red, bold font... which indicates that
the project is still pending.
>
> is there a way to get around that?
>
> hope that makes sense.
>
> tia,
> jill.
>
>


0
bob.phillips1 (6510)
4/19/2004 9:21:49 PM
Hi
try the following formula
=$A$1=TODAY()
if a1 is the cell you want the conditional format apply to

--
Regards
Frank Kabel
Frankfurt, Germany


jill wrote:
> i have conditional formatting set for projects that are still pending
> so that
>
> formula is =today()
> formatted font is red, bold
>
> once the project is complete, the date is manually entered into the
> cell (changing it from the formula =today() to an actual date (i.e.:
> 4/19/04)).
>
> the problem is that the conditional formatting at that point still
> reads the date in that cell as today's date (even though it's no
> longer a formula), and continues to show as red, bold font... which
> indicates that the project is still pending.
>
> is there a way to get around that?
>
> hope that makes sense.
>
> tia,
> jill.

0
frank.kabel (11126)
4/19/2004 9:38:27 PM
that didn't seem to work... when i enter today's date in that cell, it still shows as red, bold.  i know this will change tomorrow, but i'd like it to show the correct formatting for whatever date i enter, even if it happens to be today

suggestions

jill.
0
anonymous (74721)
4/19/2004 9:41:07 PM
sorry... still not working.  maybe i'm explaining poorly..

in cell A1, i enter =today() with the conditional formatting of that cell being

formula is =$A$1=today(
format cell as bold, re

so today, that cell shows '4/19/04

if the project closes today, i replace the formula '=today()' to a manual date, so i enter '4/19/04' in that cell

unfortunately, because =today() and 4/19/04 have the same value, the formatting still shows as bold, red until tomorrow

is there anything i can do about that

thanks so much for your help

jill.
0
anonymous (74721)
4/19/2004 10:21:01 PM
You need to create a UDF like so

Function isformula(rng As Range)
    isformula = rng.HasFormula
End Function

and change the formula to

=AND(isformula(A1),A1=TODAY())

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jill" <anonymous@discussions.microsoft.com> wrote in message
news:617D7934-ECA3-4957-9454-76C1ACBFD381@microsoft.com...
> sorry... still not working.  maybe i'm explaining poorly...
>
> in cell A1, i enter =today() with the conditional formatting of that cell
being
>
> formula is =$A$1=today()
> format cell as bold, red
>
> so today, that cell shows '4/19/04'
>
> if the project closes today, i replace the formula '=today()' to a manual
date, so i enter '4/19/04' in that cell.
>
> unfortunately, because =today() and 4/19/04 have the same value, the
formatting still shows as bold, red until tomorrow.
>
> is there anything i can do about that?
>
> thanks so much for your help!
>
> jill.


0
bob.phillips1 (6510)
4/19/2004 10:36:11 PM
that worked!  thanks very much for your help

jill.
0
anonymous (74721)
4/20/2004 10:06:01 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...