How do I do a TODAY() Function within a Nested IF AND Function

Hi,

I'm trying to create a nested IF AND function.  The difficulty I'm having is 
getting the function to calculate/include just the rows that have a date that 
is later than or equal to today.  The "today" date would move as the file is 
opened and updated.  The difficulty I'm having with it, though, is that it's 
calculating the rows that are blank or those that a date has been provided, 
but not just the date which is today or later.  

My current formula looks like:

=IF(AND($C13=$B$13,$BV13>=TODAY()),$CJ13,"")

Thanks,

Jennifer
0
Utf
4/9/2010 6:59:01 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
532 Views

Similar Articles

[PageSpeed] 16

Can you supply your whole formula?, the formula you give works fine but
only looks to display the contents of one cell not "calculate" anything.
 


Jennifer E;695294 Wrote: 
> 
Hi,
 
I'm trying to create a nested IF AND function. The difficulty I'm
having is
getting the function to calculate/include just the rows that have a
date that
is later than or equal to today. The "today" date would move as the
file is
opened and updated. The difficulty I'm having with it, though, is that
it's
calculating the rows that are blank or those that a date has been
provided,
but not just the date which is today or later.
 
My current formula looks like:
 
=IF(AND($C13=$B$13,$BV13>=TODAY()),$CJ13,"")
 
Thanks,
 
Jennifer


-- 
Simon Lloyd

Regards,
Simon Lloyd 
'Excel Chat' (http://www.thecodecage.com/forumz/chat.php)
------------------------------------------------------------------------
Simon Lloyd's Profile: 1
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=194460

http://www.thecodecage.com/forumz

0
Simon
4/11/2010 7:09:25 AM
Are the dates in column BV genuine dates (i.e. numeric
values formatted as dates) or text? If the latter then try
using
=3DIF(AND($C13=3D$B$13,DATEVALUE($BV13)>=3DTODAY()),$CJ13,"")

(though generally I dislike DATEVALUE as it is sensitive to
changes in regional settings)

Andrew



On 9 Apr, 19:59, Jennifer E <Jennif...@discussions.microsoft.com>
wrote:
> Hi,
>
> I'm trying to create a nested IF AND function. =A0The difficulty I'm havi=
ng is
> getting the function to calculate/include just the rows that have a date =
that
> is later than or equal to today. =A0The "today" date would move as the fi=
le is
> opened and updated. =A0The difficulty I'm having with it, though, is that=
 it's
> calculating the rows that are blank or those that a date has been provide=
d,
> but not just the date which is today or later. =A0
>
> My current formula looks like:
>
> =3DIF(AND($C13=3D$B$13,$BV13>=3DTODAY()),$CJ13,"")
>
> Thanks,
>
> Jennifer

0
Andrew
4/11/2010 11:15:50 AM
Reply:

Similar Artilces:

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&...

VLOOKUP Function using multiple worksheets #2
Received advise previously on this (thanks). However, I need to look up entries in another separate worksheet and it won't work. I'm using: =VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New Clients'!A2:O20,2,FALSE). It returns the persons name (great) but I need to transfer across other details too. Basically I'm summarising one worksheet onto a specified format in another. In addition, I somehow want it to only show me those clients in 'Entry 1 - New Clients' if the corresponding column (N) has the entry of "Active". Is this possible??...

return a data list depending on a value selected within a drop dow
This is the scenario - I have a number of domains each with their own unique set of capabilities - does anyone know how I can represent this using a drop-down function so that for example when I select domain1 I am only allowed to enter the domain1 capabilities see if this link gets you what you need http://www.contextures.com/xlDataVal02.html "loren" wrote: > This is the scenario - I have a number of domains each with their own unique > set of capabilities - does anyone know how I can represent this using a > drop-down function so that for example when I select d...

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...

Using the EXACT Function
I've created a simple spreadsheet. Column A is a list of 15034 used Work Order reference numbers (many values are missing); Column C is a complete list (25,000 numbers). I want to get a separate list of unused numbers, but can't get the EXACT function to work. ( I believe it should place a TRUE value in column D when the numbers match.) I've used: =OR(EXACT(C1,$A$1:$A$15034)), which I replicated down the column. Please help... Thanks, Jim Berglund I don't think EXACT will work. Try a VLOOKUP: in D1, type =VLOOKUP(C1,$A$1:$A$15034,1,FALSE) in E1, type =IF(ISNA(...

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...

Launching executables from within PowerPoint 2007
Hi, I am running Office 2007 on Windows 7. From within a PowerPoint 2007 presentation I am calling an executable (.EXE) to do something, and then return to the PowerPoint slide. The EXE file is entered into the PowerPoint 2007 slide as an object and this object is activated by the "Activate Contents" option (PowerPoint animation option). However, Windows 7 security presents a warning message with a dialog. See attached image for an example of what it look like when I try to run an executable from within PowerPoint 2007. This warning message is generated by the...

Dialogs within a static library
I've created a Win32 Static Library that includes a dialog. I've also created an MFC-based app that includes the library header, links with the lib, and attempts to display the dialog via DoModal. Even though the app links successfully, when I run the app, the dialog does not display when invoked. I traced it in debug mode and the failure is occuring within the dialog's DoModal method when LoadResource runs with the result of a ::FindResource call. That method (::FindResource) is returning NULL when attempting to find the associated resource. This is the first time I...

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...

"Advanced Find" Doesn't Function
I just updated Outlook 2003 to 2007 a few weeks ago. I created "Advanced Find" menu on the tool bar but it's never functioned in Outlook 2007. Even though I put the right e-mail address or subject, it always says "There are no items to show in this view" in the box at the bottom. When I don't put any e-mail address or subject, it shows old e-mails from 2001 and 2002 but no newer e-mails. Could you please advise what I did wrong or what I didn't do? ...

Onsave function
Hi i'am searching for a onsave function to send a e-mail. Hope anybody can help. Kind regards, Maarten Kievit You should consider using a 'server-side' plugin registered on create or update for this. - Chris http://mscrm4u.blogspot.com Easiest way is to use Workflow. Just create Workflow - fired on record update. Within workflow you can easily send an email. Jiri "Maarten" <Maarten@discussions.microsoft.com> píše v diskusním příspěvku news:3744AA7D-A6EC-4357-9160-93A1B2579826@microsoft.com... > Hi i'am searching for a onsave function t...

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? ...

Simple Counting Function
Sorry, I'm fairly new to access, but I need to figure out how to: Count the number of records in a query. and display it in a text box named txtBox. Could I get a hand with this? Thanks in advance. I assume you are using the query as a source for a form? Put the following in the control source of the text box =RecordsetClone.RecordCount -- "Loose Change 2nd Edition" has been seen by almost 7 million people on Google video "breeden.christopher@googlemail.com" wrote: > Sorry, I'm fairly new to access, but I need to figure out how to: > > Count ...

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...

all function formulas syntax
i want list on all excel 2003 functions, formulas and syntax with simple examples thank u There are some useful files on this page http://www.rondebruin.nl/id.htm -- Regards Ron de Bruin http://www.rondebruin.nl "azeem mansoori" <azeem mansoori@discussions.microsoft.com> wrote in message news:A0557C3A-AD82-4A7F-ADC1-D6255EFB7E6E@microsoft.com... >i want list on all excel 2003 functions, formulas and syntax with simple > examples > thank u ...

IF Function Help #3
I have a project that requires me to calculate bonuses for employees. I am pretty sure that I need to use an IF function. Here is the question: If the project fees do not exceed estimates the manager receives an 100 bonus. Algebraically, this is Bonus=(actual-projected)*bonus rate if (actual-projected)>0, 100 otherwise. Create a formula that calculates the project manager bonus. This is a completely different language that I just can't seem to decipher. If anyone can help, that would be great! =if(actual - projected > 0, (actual - projected) * bonusRate, 100) -- HTH, Barb R...

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...

Any way to use "standard" Excel functions inside VBA functions
I am new to this group, so don't mind if I ask somthing that is too easy for you. My question is whether I may use all "standard" Excel 2007 functions. I would like to find interval in which some function returns "#NUM!" although it must not, because all parameters are inside "permited ranges". My idea is to start with some interval [A, B] for which A is "acceptable" argument and B is "unacceptable" argument, and, by halving it (C=A+(B-A)/2.0) and asking : IF C is "unacceptable" argument THEN B=C ELSE A=C WHILE (B-A...

How do I embed FIND function in a cell
I am creating a large excel file that has lots of info that several people will need to search through that have limited computer knowledge. To make it easier on them is there a way to embed the FIND function into a cell that is easy for them to find and use. They can't seem to remember that Ctrl+F gets them what they need and they seem to lose the icon from their tool bars every time I place it there. well you could always just make a BRIGHT RED cell that tells them how to find it. "fhires" wrote: > I am creating a large excel file that has lots of info that several ...

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 View to Display All Task between Today-30 and Today+30
Is it possible to create a View in Task folder such that all tasks due between Today-30 and Today+30 are displayed? In the Advanced Filter setting, I have tried the following setting in vain: Field: Due Date Condition: between Value: date()-30 and date()+30 Thanks for your very kind help in advance. Regards, Armstrong ...

Space within cells
How do you create a space between sentences within one cell? Use Alt + <Enter> to add a carriage return to your cell. -- HTH... Jim Thomlinson "J" wrote: > How do you create a space between sentences within one cell? Hit the space bar? If you mean you want to force the next sentence onto a new line within that cell, hit and hold the alt key when you hit Enter. If you see a little box where that alt-enter is, then you have to change the wrap text property. Rightclick on that cell Format Cells|Alignment tab|check "Wrap text" J w...

today()
I wish to have a formula in a3 such as: "Concatenate(a1,a2)" where a1 contains the text "today is" and a2 contain the function today(). Unfortunately, concatenate gives me: "today is37878", 37878 being the serial number of the date. I have tried formatting each cell every which way to get the date as it is shown in cell a2, but to no avail. There also seems to be no function to return the serial number back to a readable date. any suggestions? Thanks Bern, A couple ways to do that. ="Today is " & TEXT(TODAY(),"mm/dd/yy") This re...

reprogram or block function keys
I would like to know if there is a way to either reprogram or block some of the function keys in the POS screen. I have had many complaints about hitting the F1 key in the middle of a sale. Even better yet is turning off the printer when trying some other function key stroke and not realizing it till after the sale and there is no print out. To set up a security level in the POS program 1. On the transaction screen, click on the blank field located to the left of the F7: Set Customer function key. You should see the Security Mode icon displayed both on the field that was jus...