Need help with a project sheet, need date to show/not show based o

Hello,

I have the following formula in cell S2 
=IF(B2="","",DATE(YEAR(B2),MONTH(B2),DAY(B2)+31))  
This takes a date in cell B2 and adds 31 days to it and displays it.

What I need to do is to NOT have cell S2 show any date if anything is in 
cell D2.

I'm not very adept at Vis Basic if that what it will take to do it.

Thanks
Max    
0
Utf
12/8/2009 8:55:02 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
1385 Views

Similar Articles

[PageSpeed] 55

Hi,

No need for code

=IF(OR(B2="",D2<>""),"",DATE(YEAR(B2),MONTH(B2),DAY(B2)+31))

Mike

"Max" wrote:

> Hello,
> 
> I have the following formula in cell S2 
> =IF(B2="","",DATE(YEAR(B2),MONTH(B2),DAY(B2)+31))  
> This takes a date in cell B2 and adds 31 days to it and displays it.
> 
> What I need to do is to NOT have cell S2 show any date if anything is in 
> cell D2.
> 
> I'm not very adept at Vis Basic if that what it will take to do it.
> 
> Thanks
> Max    
0
Utf
12/8/2009 9:06:13 PM
In order to add ONE full month to a date [if this is your intension] I would 
suggest using: =EDATE(B2,1)
Check your formula against the above
PS: You may need to install the Analysis Toolpak if you are using Excel ver. 
prior to 2007.
Micky


"Max" wrote:

> Hello,
> 
> I have the following formula in cell S2 
> =IF(B2="","",DATE(YEAR(B2),MONTH(B2),DAY(B2)+31))  
> This takes a date in cell B2 and adds 31 days to it and displays it.
> 
> What I need to do is to NOT have cell S2 show any date if anything is in 
> cell D2.
> 
> I'm not very adept at Vis Basic if that what it will take to do it.
> 
> Thanks
> Max    
0
Utf
12/8/2009 9:18:01 PM
Mike,

Initially it worked the opposite of what I wanted it to do, but after 
changing the <> to a = it seems to work fine.

Thanks for your help! 

"Mike H" wrote:

> Hi,
> 
> No need for code
> 
> =IF(OR(B2="",D2<>""),"",DATE(YEAR(B2),MONTH(B2),DAY(B2)+31))
> 
> Mike
> 
> "Max" wrote:
> 
> > Hello,
> > 
> > I have the following formula in cell S2 
> > =IF(B2="","",DATE(YEAR(B2),MONTH(B2),DAY(B2)+31))  
> > This takes a date in cell B2 and adds 31 days to it and displays it.
> > 
> > What I need to do is to NOT have cell S2 show any date if anything is in 
> > cell D2.
> > 
> > I'm not very adept at Vis Basic if that what it will take to do it.
> > 
> > Thanks
> > Max    
0
Utf
12/8/2009 9:27:01 PM
You can also shorten your formula to:
=IF(OR(B2="",D2=""),"",B2+31)

Regards,
Fred.


"Max" <Max@discussions.microsoft.com> wrote in message 
news:51A83D8C-9747-407C-911E-EC1439314348@microsoft.com...
> Mike,
>
> Initially it worked the opposite of what I wanted it to do, but after
> changing the <> to a = it seems to work fine.
>
> Thanks for your help!
>
> "Mike H" wrote:
>
>> Hi,
>>
>> No need for code
>>
>> =IF(OR(B2="",D2<>""),"",DATE(YEAR(B2),MONTH(B2),DAY(B2)+31))
>>
>> Mike
>>
>> "Max" wrote:
>>
>> > Hello,
>> >
>> > I have the following formula in cell S2
>> > =IF(B2="","",DATE(YEAR(B2),MONTH(B2),DAY(B2)+31))
>> > This takes a date in cell B2 and adds 31 days to it and displays it.
>> >
>> > What I need to do is to NOT have cell S2 show any date if anything is 
>> > in
>> > cell D2.
>> >
>> > I'm not very adept at Vis Basic if that what it will take to do it.
>> >
>> > Thanks
>> > Max 

0
Fred
12/8/2009 11:01:54 PM
Reply:

Similar Artilces:

leading zero value help
Hello, I have two tables with a text field and the value in the field range from 50 to 9999. 50 is 2 characters, 100 is 3 characters and 9999 is 4 characters. I ran into a problem when referencing these values in calculation. How or what can I do to put two leading zero to 2 character field and one zero to 3 character field on all existing records? (Maybe a macro, I don't know) There are currently over 2000 records so it is sufficent to manually change them all. Any help is appreciated. Example: 50 = 0050 200 = 0200 950 = 0950 >>I ran into a problem when referencing thes...

Please help. Question about querying a subform
I asked this yesterday and see all kinds of questions around me being answered and suppose i just did not word this correctly. I have a table of lab results. It is related to my patient table. It is a one-to-many relationship. I have a date field, which is the date of last labs. I am trying to build a query that will pull only the most previous lab from each patient, with the criteria of it having been more than 6 months ago. I do not want to pull lab dates from 10 months ago, if there is one within the last 6 months. This is for a Dr and it is a priority that I get it d...

Check Names
Using Outlook 2000 under Windows 2000 Server. When I type a partial name in the To: field on a new email, I get the red squiggly line indicating there are multiple names matching that pattern. When I hit Send, I get the Check Names window showing a list of matching names. However the only names that show up in the list are from the Global Address List, and none from my Contacts. Under Tools, Services, Addressing tab, I have two entries in the "When sending mail, check names using these address lists in the following order". First is the Global Address List, and second is Contacts,...

Individual user in my company needs to have a different domain nam
We are using Exchange 2003, one domain, less than 200 users. One user needs to have his e-mail domain name different from the rest of the company. I was wondering if there was a way I could have his domain name appear to be from another non-existant domain name. For example our company domain name is company.com and his domain name needs to be othercompany.com where othercompany.com does not currently exist. Any insight into this question is welcome and appriciated. Thanks, what is the objective? you can change his smtp address, but if he sends out Internet email with a reply address...

Newbie
Hi all I am not sure if this is able to be done, but I will explain what I am trying to do: I have the following cells: Worksheet 0 A B C D E 1 2 3 4 5 A B C D E 6 7 8 9 10 Where: A1 = Calculation in row 1 worksheet 1 B2 = Calculation in row 1 worksheet 2 C3 = Calculation in row 1 worksheet 3 D4 = Calculation in row 1 worksheet 4 E5 = Calculation in row 1 worksheet 5 and A6 = Calculation in row 2 worksheet 1 B7 = Calculation in row 2 worksheet 2 C8 = Calculation in row 2 worksheet 3 D9 = Calc...

inventory shows as net income under Equity on balance sheet
Im new to MS Money Small Business 2004. At setup, the input of inventory shows up as net income under Equity on my balance sheet. How can I un-do or re-do this? Thanks in advance ...

Need to extrcat data from one Excel file to an other
Hi there I'm looking form a solution to press on button in a Excel sheet which ill exctract some datas from an other Excel sheet which can be close or opened. It should beworing on Windows XP, 2003, Vista and 7 as well with 32 bits and 64 bits. Thanks in avance for your advise and best regards Tra Browse through the example codes found at Ron de Bruin's site. http://www.rondebruin.nl/tips.htm Look at the 'Copy/paste/merge" section. Should be some good material to start with. Gord Dibben MS Excel MVP On Sat, 28 May 2011 19:53:03 +0200, Tra Sumaka <trasumak...

? do i create raffle result sheet
Hi you may give some more details. Also post your question as body of your message :-) -- Regards Frank Kabel Frankfurt, Germany "daff" <daff@discussions.microsoft.com> schrieb im Newsbeitrag news:F9C6C5AA-D559-48DB-8231-8DBEA63C5A7E@microsoft.com... > Peo This one's for you<g> Gord On Mon, 20 Dec 2004 09:01:04 -0800, "daff" <daff@discussions.microsoft.com> wrote: lol -- Regards Frank Kabel Frankfurt, Germany "Gord Dibben" <gorddibbATshawDOTca> schrieb im Newsbeitrag news:d74es0pofu129bgf47m4ma4lc6poviufbr@4ax.com....

outlook2003 help
Hi, all: I have a big 2gb file outlook.pst of outlook 2000, I could no longer work with this file in outlook2000. I know the file limit in 2003 is bigger, so I wonder I could use the 2003 open this file and still be ablt to modify it, as for now in 2000, I can read the information but not to modify like tasks. any help will be very much appreciated. thanks. David No you cannot. The file must be open to export to Outlook 2003 so you will have truncate the file first with the repair utility. You will still lose part of the pst file even if you eventually export to Outlook 2003. &quo...

Is there a way to protect just one cell, not the whole sheet?
The books I read seem to indicate so, but say you first must turn on the 'Protect Sheet' option. When I do that, everything is protected. Any help much appreciated. Hi try the following: - first select all cells you don't want to protect - goto 'Format - Cells - Protection' and uncheck 'Locked' - now protect your sheet -- Regards Frank Kabel Frankfurt, Germany "Fred Exley" <fexly221@msn.com> schrieb im Newsbeitrag news:10hsd5t42ghdt65@corp.supernews.com... > The books I read seem to indicate so, but say you first must turn on the > &...

SumProduct with date and time
I have finally found that with my formulas the calulations are based on the time. Can someone help me find the best way to correct this without using macros to strip the time out of the data. Bob 09/08/2007 01:05 13/08/2007 00:00 Bob Bill 10/08/2007 23:39 Bill Kyle 11/08/2007 07:36 Kyle Bob 16/07/2007 20:39 Bill 27/07/2007 19:59 Kyle 18/07/2007 16:51 Bob 14/07/2007 16:31 Bill 21/06/2007 16:46 Kyle 30/06/2007 16:55 Bob 12/06/2007 01:05 Bill 13/06/2007 23:39 1 2 0 1 1 Kyle 14/06/2007 07:36 1 1 1 1 1 Bob 12/01/2007 20:39 1 1 2 0 1 Bill 13/08/2006 19:59 Kyle 14/05/1999 16:51 These are t...

please help
I am a long time member of this newsgroup, but I've recently ran into some financial problems http://www.geocities.com/bumpcycle/donations.html ...

Copying to new sheet
Hi, I have 12 WORKBOOKS (not worksheet) all the same and you can mak entires into them of stock. I also have a master workbook - exactly the same as the 12 others. Is there a way of copying whatever someone enters into any one of th 12 into the master one automaticaly? for example, i enter something into book 1 and book 6 and then i loo at the master one and it's been updates for me with those entrie aswell. many thanks, Simon. :confused -- Message posted from http://www.ExcelForum.com If you have linked the master workbook to the others with formulas thi should automatically oc...

Rename name so formulas show name change
Excel 2003 Cell A1 is named MyCell. Cell A2 has formula "=MyCell". I rename cell A1 in the toolbar to NewCell. Cell A2 still has the formula "=MyCell". How do I rename cell A1 to NewCell and have it automatically change cell A2 to the formula "=NewCell"? Thank you for your help. Download Jan Karel Pieterse'e Name Manager here: http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp -- Kind regards, Niek Otten Microsoft MVP - Excel "John" <no.email@no.email.com> wrote in message news:ORne6k3$GHA.4592@TK2MSFTNGP03.phx.gbl... | Excel 2003 |...

XML Insert data C# ASP.net 2.0 Need to know how to insert by speci
Hello, I have the following XML Schema: <?xml version="1.0" encoding="utf-8"?> <Schedule> <Day Name="Monday"> </Day> <Day Name="Tuesday"> </Day> <Day Name="Wednesday"> </Day> <Day Name="Thursday"> </Day> <Day Name="Friday"> </Day> <Day Name="Saturday"> </Day> <Day Name="Sunday"> </Day> </Schedule> I want users to be able to input the following tiomeblocks for a scheduling s...

Change Label based on Two Combo Boxes
Hi, I currently have this code in AfterUpdate event for Combo2, hoping it would change label1, but it does not work. : If Me.Combo1.Value = "State" And Me.Combo2.Value = "2008" Or "2007" Or "2006" Then Me.Label1.Caption = "Select a State:" ElseIf Me.Combo1.Value = "City" And Me.Combo2.Value = "2009" Or "2010" Or "2011" Then Me.Label1.Caption = "Select a City:" Else Me.Label1.Caption = "Select a Number:" End If The label does not change when I make changes to the combo...

Help! #6
I have a spreadsheet which consists of a separate worksheet for each month. Each worksheet is holding data about the number of days a person is absent and the number of occasions. For example, if a person has a whole week off this will show as 5 days absent on one occasion. At the end of each sheet is a column with totals the number of days and another totals the number of columns. This data is then transferred to a summary sheet which comprises pairs of colums containg the total days lost in each month, and the total instances each month. My problem comes when somebody is absent over ...

please help 12-22-09
I am having trouble with my form pulling info from my query. It will run fine if I don't have the forms!formname!controlname there but once I add that it won't pull at all. Does anybody have a clue why its not pulling? On Tue, 22 Dec 2009 06:44:01 -0800, MsWatts <MsWatts@discussions.microsoft.com> wrote: >I am having trouble with my form pulling info from my query. I think you mean "my query pulling info from my form." >It will run fine >if I don't have the forms!formname!controlname there but once I add that it >won't pull at al...

Java vs. C++ (Date class)
I'm porting some Java code into C++. Which class I should use instead Date class? Note that Date support miliseconds. Also, which class I should use instead of Vector class? std::vector? "Petar Popara" <my.fake@mail.net> wrote in message news:uG6VGpUfFHA.2424@TK2MSFTNGP09.phx.gbl... > > I'm porting some Java code into C++. Which class I should use instead Date > class? Note that Date support miliseconds. Oh dear. Both the Java and the C++ date classes do assorted odd things with time zones and "daylight saving"; neither is documented fully or ...

Assets Depreciation in Projects
We have assets that we are using in several different projects. The depreciation of these assets should be allocated as a cost on the respective project they are being used in. We have purchased the GP Asstes management and project accounting. But we are told that they are not linked, and therefor the depreciation of these asstes has to be manually entered on each project one by one. How true is that? Rana, Currently there is no integration between Project Accounting and Fixed Assets. Therefore, you will need to manually allocate the depreciation to each project. I am not sure what...

RMS should show what bar code it is going to print
Okk when a item is made in RMS and the customer makes his own barcode and selects a barcode format to print it should show at the bottom of the screen what barcode is being created(as in the case of UPC, EAN it has a check Digit) That way the customer can put in the check digit in the number so it will scann after printing. ---------------- 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 message pane. If you do not see the button, follow this link to open the...

Excel to XML: Need some help fine tuning a macro
Hey all, I've downloaded a Macro http://meadinkent.co.uk/ and have been editing it get the exported XML to appear as required, however I can't seem to get the 2 of my fields to display correctly. There are two fields which have numbers in but are formatted as text because I do not want them rounded up. One cell has -0.08162498 in it and in the exported XML doesn't even appear. Another cell has 51.51891794 in it and in the exported XML appears as 52. My question is how can I get the information to appear as I've typed it in rather then rounding it? I have cha...

Quick debugging help needed
I'm on a 2-hour deadline to get this sub working, and, since I'm new to VBA, I don't know enough to know which particular very stupid thing I'm doing. Having said that, if some kind soul can point me in the right direction to fix this, blessings will be upon your head. This stops in 'week2' with runtime error 424 - Object required - it's working on a scheduling worksheet and is looking for overtime hours in particular cases - hence the values 1, 2 or 3 in column W in rows where column AT is X. I know I don't yet know if number values require ""...

error in excel help. how to select a cell after clicking the Select All button?
Hello "To see all the relationships on a worksheet, in an empty cell type = (equal sign), and then click the Select All button. Select the cell, and press Trace Precedents twice." is written in Excel 2002 help. It's not possible to "Select the cell" following the procedure. Or How to "select the cell" in the procedure? begin 666 0486.gif M1TE&.#EA%0`4`)$``/___V9XI0```)D`F2'Y! ``````+ `````5`!0```)* MC(^I`>T/GP&BUHBG<'MCIW%-]VF61Z'0A%DE,[:GQU+M@ \T,T-"KFO45)P> @8/AP96"R5TI)6MIBQ$@H]I'P3EEM-UO[KA9D10$`.P`` ` end Ma...

how to create a calendar to select a date in access 2007
I want to select a date from a pop up calendar on the form. Access 2007 automatically adds a date picker to a Date/Time field. -- Lynn Trapp MCP, MOS, MCAS "keithteri" wrote: > I want to select a date from a pop up calendar on the form. ...