If Formula , Adding Days to a Date

	    A	      B
1	06/06/10	

I want to make a formula that calculate as following;

in Cell (B1) if the date in future the result will be [Enrolled], if not it 
will be Cell (A1) + 730 Days
0
Utf
3/27/2010 9:14:01 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
1000 Views

Similar Articles

[PageSpeed] 36

On Sat, 27 Mar 2010 02:14:01 -0700, Khalid A. Al-Otaibi
<KhalidAAlOtaibi@discussions.microsoft.com> wrote:

>	    A	      B
>1	06/06/10	
>
>I want to make a formula that calculate as following;
>
>in Cell (B1) if the date in future the result will be [Enrolled], if not it 
>will be Cell (A1) + 730 Days

Try this formula in cell B1:

=IF(A1>TODAY(), "[Enrolled]", A1+730)

Format as date.

Hope this helps / Lars-�ke
0
Lars
3/27/2010 9:29:20 AM
If A1 has a fix date and you are going to type(!) another date in B1 - then, 
B1 cannot have a formula - therefor try the following Worksheet Event-Macro:
-----------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
         If Target.Address = "$B$1" And Target <= Date Then [B1] = [A1] + 730
    Application.EnableEvents = True
End Sub
------------------
Micky


"Khalid A. Al-Otaibi" wrote:

> 	    A	      B
> 1	06/06/10	
> 
> I want to make a formula that calculate as following;
> 
> in Cell (B1) if the date in future the result will be [Enrolled], if not it 
> will be Cell (A1) + 730 Days
0
Utf
3/27/2010 10:14:01 AM
Thank you so much,

Let me show you what I am doing, I really got lost;  I don't know what I 
should do next.

I am doing a Sheet, so we can identify which one of the employee is certify 
and who is not in (Driving License for Example)  it expire every two years.  
Every two years the Employee should take the course again.  Some employee are 
not require to take the course.  I just want to make a data to know every 
employee into witch category he belong.

Valid - Expire - Enrolled - Not Enrolled - Not Applicable

Badge	Emp. 	Start Date	End Date
65863	Mone M	01/31/10	01/31/10
65864	Kim L	02/02/08	02/02/08
65865	Henryk  J	07/27/08	07/27/08
65866	Sami B	Blank	Blank
65867	Dep R	N/A	N/A

First Employee the Result is Valid
Second Employee the Result is Expired,
Third Employee the Result is Enrolled
Furth Employee the Result is Blank.  (Not the I wrote Blank, which I don't 
want that)
Fifth Employee the Result is Not Applicable ( I wrote N/A and I don't want 
to change it.

I tried more than one formula and you help in creating this formula.



1-=IF(C10>TODAY(), "Enrolled", C10+730).
2-=IF(F6<TODAY(),"Expired","Valid")

Please help me, Please
-----------------------------------------------------------------------------------
"מיכאל (מיקי) אבידן" wrote:

> If A1 has a fix date and you are going to type(!) another date in B1 - then, 
> B1 cannot have a formula - therefor try the following Worksheet Event-Macro:
> -----------------------------------------------------------------------------------
> Private Sub Worksheet_Change(ByVal Target As Range)
>     Application.EnableEvents = False
>          If Target.Address = "$B$1" And Target <= Date Then [B1] = [A1] + 730
>     Application.EnableEvents = True
> End Sub
> ------------------
> Micky
> 
> 
> "Khalid A. Al-Otaibi" wrote:
> 
> > 	    A	      B
> > 1	06/06/10	
> > 
> > I want to make a formula that calculate as following;
> > 
> > in Cell (B1) if the date in future the result will be [Enrolled], if not it 
> > will be Cell (A1) + 730 Days
0
Utf
3/27/2010 11:01:01 AM
Khalid

My observations, based on your posts:
You have identical start and end dates, plus blank or n/a cells. 
Using 730 days to calculate the end date will result in an error, if the 
term includes a leap year

Suggested column Headings in row 1
Column A: Result (formula) 
Column B: Badge (entered value)
Column C: Name (entered value)
Column D: Start Date (entered value)
Column E: Term in years (entered value) – optional entry, can be 
incorporated in formula
Column F: Condition (value in row 2 to 6: not enrolled, not applicable, 
enrolled, valid, expired)
   Note: Columns E or F may be eliminated, by including the applicable 
values in the formula.

Criteria 1: start date = blank, Formula returns  “not enrolled”
Criteria 2: start date = n/a, Formula returns  “not applicable”
Criteria 3: start date > today, Formula returns:  “enrolled”
Criteria 4: end date  > today, Formula returns:  “valid”
Criteria 5: end date  < today, Formula returns:  “expired”

The formula assuming these column headings and criteria are used:
=IF(ISBLANK(D2),F2,IF(ISTEXT(D2),F3,IF(D2>TODAY(),F4,IF(EDATE(D2,E2*12)>TODAY(),F5,F6))))





"Khalid A. Al-Otaibi" wrote:

> Thank you so much,
> 
> Let me show you what I am doing, I really got lost;  I don't know what I 
> should do next.
> 
> I am doing a Sheet, so we can identify which one of the employee is certify 
> and who is not in (Driving License for Example)  it expire every two years.  
> Every two years the Employee should take the course again.  Some employee are 
> not require to take the course.  I just want to make a data to know every 
> employee into witch category he belong.
> 
> Valid - Expire - Enrolled - Not Enrolled - Not Applicable
> 
> Badge	Emp. 	Start Date	End Date
> 65863	Mone M	01/31/10	01/31/10
> 65864	Kim L	02/02/08	02/02/08
> 65865	Henryk  J	07/27/08	07/27/08
> 65866	Sami B	Blank	Blank
> 65867	Dep R	N/A	N/A
> 
> First Employee the Result is Valid
> Second Employee the Result is Expired,
> Third Employee the Result is Enrolled
> Furth Employee the Result is Blank.  (Not the I wrote Blank, which I don't 
> want that)
> Fifth Employee the Result is Not Applicable ( I wrote N/A and I don't want 
> to change it.
> 
> I tried more than one formula and you help in creating this formula.
> 
> 
> 
> 1-=IF(C10>TODAY(), "Enrolled", C10+730).
> 2-=IF(F6<TODAY(),"Expired","Valid")
> 
> Please help me, Please
> -----------------------------------------------------------------------------------

0
Utf
3/27/2010 4:19:01 PM
Reply:

Similar Artilces:

Adding floating point numbers
hi the value 0.1 in the computer memory is not equal 0.1 so if u add it 10 times the result is diffrent then 1.0: double value=0.1, result; ressult=value+value+value+value+value+value+value+value+value+value; //result<1.0 BUT: result=value*10; //result==1.0 WHY??? another example: float value=0.1, result; ressult=value+value+value+value+value+value+value+value+value+value; //result==1.0 WHY??? can anyone explain it? thx On Thu, 28 Jun 2007 10:18:05 -0700, rsobies <rsobies@discussions.microsoft.com> wrote: >hi > >the value 0.1 in the computer memory is not equal 0.1...

True or False Formula?
Hi Everyone. I've just got myself Excel and I'm trying to create a simple spreadsheet. What I need to do is have C calculated automatically. I have searched Google for a hint, but I don't know Excel that well enough yet plus English is my second language. A1>B1=C1 If A is greater than B, C is the answer. 1>5=False 1>0=True 1>1=Equal or 0 from what I read Very much thanks to anyone who can help learn this. Joanne Ramirez Hello. I have worked out part of my probelm and realized I need help with more. I am trying to learn this. =(A1>B1)OR(A1<B1)OR(A1=B1) ...

Non-VBA formula to find 2nd Sunday of a given month
Can anyone help me write a formula to find the date of the second sunda in a given month? Thanks in Advance, Dav ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com David, Assuming you have a date in A1, this gives the 2nd Sunday of that date =DATE(YEAR(A1),MONTH(A1),1)+MOD(8-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)+7 -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "DavidObeid&q...

adding items to the action pane in GP 10
We need to offer the capability to add additional windows to the "Go To" button in the Action Panes, beyond the (very) limited options currently available. For example, in the Accounts view of the Financial Center, there is no way to Add to the "Go To" a link to the "Actual vs. Budget Inquiry Window". Lacking this feature requires the user to either return to the Financial Center page, or to use the drop -down menus (Inquiry >> Financial >> Actual vs Budget). Either approach requires the user to re-enter the account number, essentially starti...

Wndws XPsp3: Auto Update ON but system nags all day to Install Upd
I have my Auto Update set for AUTOMATIC to download/install at 3 am daily BUT I get at least 4-5 messages per day that "Updates have been found formy computer". Constantly updating/restarting. This is driving me CRAZY. Please help. ME wrote: > I have my Auto Update set for AUTOMATIC to download/install at 3 am > daily BUT I get at least 4-5 messages per day that "Updates have > been found formy computer". Constantly updating/restarting. This is > driving me CRAZY. Please help. We now know the following blanks in the puzzle... Windows XP ______ ...

AD authentication
Hello, I would like to ask you what is the minimum packet size for AD (ldap) authentication between WinXP Pro and Win2003 R2. I am working in the brancj office and we are connected with main company through VPN - here is located server farm with DC. Sometimes we have troubles with domain logons. I found that big packets (more than 1500) can not by delivered to the server. When I reset ISP router Cisco 1800, authentication is again ok. ISP gived me status, that his devices wokrs fine and problem is on the our side, because he tested ping after device restart :( Tommorow I hav...

Excel Concatenate formula problem
Hi, Im trying what i believe should be a simple formula, i have a list o 500 numbers e.g 25452820 25452821 25452822 and so on.... I need to insert a "0" infront of all the numbers for which i am usin the formula =CONCATENATE(0,A1:A500) and this works fine. My problem being i also need all the numbers to have a space after th 3rd digit???? e.g 025 452820 025 452821 and so on.... -- Message posted from http://www.ExcelForum.com hi, Easier solutions may exist but this worked for me. Assuming your concatenated result is in C8. =CONCATENATE(LEFT(C8,LEN(C8)-3)," ",RIGHT...

I need a formula to calculate 2-3 columns but skip a column if it has a zero
I'm building a Golf League spreadsheet and we want to calculate handicaps starting with the second week (calculates 2 weeks) and then after the 3rd week calculates every 3 weeks. Columns B-S are the individual weeks scores for each golfer (golfers listed by row 4-33), each column (b-s) has a formula which pulls the golfers score from the input worksheet(weekly input) to this worksheet(scorecard). Starting with Column V we are trying to have the running handicap for the year which changes each week. Column W is the first week handicap =AVERAGE(B4-36)*0.8 (this ones easy) Column X is the se...

sumif formula
Hi is it possible to write a sumif formula to get data from two sheet at the same time. I want to look for "A4" from Sheet1 & Sheet2 at the same time. the idea here is if "A4" is in both tabs than give me the sum figure. Thanks Just string 2 SUMIFs together: =SUMIF(Sheet1!A1:A10,"A4",Sheet1!B1:B10)+SUMIF(Sheet2!A1:A10,"A4",Sheet2!B1:B10) -- Biff Microsoft Excel MVP "Rohit" <Rohit@discussions.microsoft.com> wrote in message news:C8120B7E-0E6C-4CBE-BE68-B2126E0B2C00@microsoft.com... > Hi > is it possible to write a sumi...

Auto fill keeps adding 1 digit or 1 cell
When I try to drag a formula or value down or across the spreadsheet in Excel 2002 (Win XP Professional)it automatically adds 1 didgit to the value or references the cell next door to calculate the formula. How can I make the drag and autofill more accurate? Hi Try draggin with the right mouse button. When you get to the end of your range you get a list of fill options. -- Andy. "Ricardo" <anonymous@discussions.microsoft.com> wrote in message news:2b0bc01c46826$09b920f0$a501280a@phx.gbl... > When I try to drag a formula or value down or across the > spreadsheet...

Unable to set Date Range on UPR_Month_End
I am in Dexterity, and I am opening the form UPR_Month_End and setting the date range unfortunatly the set start date and end date are being wiped out before the form makes it to the user. My code worked before the last service pack from Great Plains, and I have tried many things to get it to work. Basically what i am doing is on a button click on one of my forms I am trying to set the date range on the period end payroll reports screen to the beginning and end of a quarter for convienence because the ability to run quarter end reports was removed from Great Plains. Below is the code...

Display Date from Unbound Field in Form in a Query
Just had an issue that in 10 years of using Access never saw - just wondering if I just never encountered it or what... I have a form running a query, the user enters a date. The report needs to show the date - so the query has a field like this: report date: [Forms!]![frm_report]![txtReportDate] I had a problem exporting to Excel - seems the direct placement in the query led to characters that Excel could not understand. I actually had another date field from the form that I did a date add on, it worked fine. I eventaully put the form reference in a Format, the issue was gone. The...

Macro help: Adding Specified Blank Formatted Rows Automatically?
How do I make a macro that will prompt with a dialog box to copy the rows a certain number of times. For example, if I have 2 rows of data and I want to be able to specified any number of copies of the 2 rows blank without the data but maintain the formats (colors, condition, formulas, code, etc.). I want to add the rows to the existing worksheet. Also, I would want to this macro to be flexible to maintain it�s function if I add or delete columns from the chart. See sample chart File Attached: http://www.exceltip.com/forum/attachment.php?postid=279064 (question5 sample1.xls) ----------------...

Interesting challenge to highlight instances of >10 consecutive days scheduled
This is a multi-part message in MIME format. ------=_NextPart_000_0034_01C3E99D.D9DCF660 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable In column A (A12:A377) I have this year's dates by day. In a cell in = another column, let's say cell D1, I want to put in a calculation that = will present the word "OVER-SCHEDULED" when there is ever a time when = someone has been scheduled in to work more that 10 consecutive days in a = row. The calculation must look for the following schedule codes to = determine a work shift: D,E,N...

Handling bill-related dates
Hello (Using MS Money 2007) I would like to register the following date when managing bills: 1) The emission date 2) The due date 3) The payment date, which can be before or after the due date. Is it possible? Thanks a lot Pick one. "EMoscosoCam" <EMoscosoCam@discussions.microsoft.com> wrote in message news:68EE3D99-8372-4C9C-907B-80282DA47070@microsoft.com... > Is it possible? I would like to have all 3 dates registered, but I do not know if MS Money allows me to do so. Moreover, If a bill arrives, I would registered the emission date and the due date. But when...

Adding an email account to Outlook Express 6
I have followed the directions provided in adding my account. The email address I am trying to add is actually a Netscape email account (provided at work.) I would rather use Outlook for my emails for obvious reasons. Set up as POP3, no auth req. I am getting an error message when I click send/rec and when I try to send an email from Outlook. Anyone?? The message could not be sent because one of the recipients was rejected by the server. The rejected e-mail address was "my email addrss.net'". Subject '', Account: 'mail.wacoinc.net', Server: 'smtpout...

What is the formula to convert a whole column containing date to text?
What is the formula to convert a whole column of date to text? If I am to do it cell by cell, I need to press F2 then put a --> ' in front of the date. Is there a way I can do it with formula? I have tried CONCATENATE to combine column a which contains ' with date 12/31/03. Column A Column B ' 12/31/03 =CONCATENATE(A1,B1) However, the end result I got is as follow. '37986 instead of '12/31/03 Please help! Hi Caine one way: =TEXT(A1,"mm/dd/yyyy") where A1 stores your date value. Copy this down ...

formulae #4
I require a formula that will count how many days between two dates.eg 23/05/02 and23/05/03. Using the rcommended formula days360(the two dates here)gives the answer 1 as in one year.........help meeeee please! You want the DateDif() function. It's undocumented, except in XL2k, so Chip Pearson has fully explained it in his web site: http://www.cpearson.com/excel/datedif.htm -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------...

Re: how get rid of cells with unused formulas
Actually a much older solution is better for suppressing zero values selectively -- format with a custom format so that the third parameter is empty which is a zero value. This allows you to be selective. Format, cells, number #,###.00;-#,###.00;;@ the fomat of custom formatting is positive numbers; negative numbers; zero; text you can override positive, negative but those are the defaults. see the topic "Create a custom number format" in your Excel Help. related: http://www.mvps.org/dmcritchie/excel/formula.htm --- HTH, David McRitchie, Microsoft MVP - Excel [s...

how to make gaps in plotted data when cell has formula
I have a simple data set with a value for each month except June. I want the chart to reflect a gap for June, not a zero value. Although I did tools, options, chart, make empty cells plot as gaps, it still plots the June value as a zero. In an attempt to get rid of div/o error, I have a formula in the data set as If (b5=0, "", c5/b5) . What I'm plotting is the quotient c/b. We have determined that the formula is what is causing the problem. Interestingly, if I open this same worksheet (as is) in quattro pro, the gap appears in the chart. Is there a different way to do th...

How to Add New AD user "attribute"?
We want to add a couple of new "attributes" to our AD user accounts. I am not sure that "attribute" is the correct wording but here is what we want: Like you have these "attributes" by default for each user Description: Office: E-mail: we would like to add these Employee#: CostCenter: I am quite certain that this will be a Schema modification but I am just not sure how to properly do this? Any help is greatly appreciated. Thanks, Fred you might want to consider using the custom attributes for these... "Fred Yarbrough" <fcyarbrough@yahoo.com...

Adding user CAL to Action Pack CRM 3.0 SBE
Hello, We have CRM 3.0 SBE installed from our Action Pack subscription. We have need to go beyond the 10 included user licenses and have purchased 2 more SBE user CALs through Microsoft Open Licensing (along with a 5-pack of 2003 SBS CALs we needed). The problem is on the eopen website where I get the license keys for the 2003 SBS CALs, the new CRM user CALs are listed but there is no license key. I have no idea how to get the key needed to add the licenses we just purchased to our current CRM installation. I can't even seem to find anyone to call who even understands the problem. ...

Adding interest to an invoice that is past due...how?
I, like many other businesses, have a problem with customers paying after the terms of the invoice. I would like to know how to handle adding an interest penaly to the invoice and then mailing it back out to the customer when payments are not received by the terms date on the invoice. Does Money 2004 Small Business have a way to do this? I have been with this product since Money 99 Small Business, and it does not seem that any significant features have been added to the Small Business version of this product. Thanks, Chris ...

set first day of the week
hi, how to change the first day of the week (that my boss use a weekly reports) set datefirst 6 statment, completed succesfully but with no changing of default setting ( 7 ) ,any help please thanks ...

Time / Date on X axis
I'm trying to plot a series (i.e. groundwater elevation) on the Y axis against the time and date on the X axis. The time and date are in separate columns. I can't get the X axis labels to appear correctly. The entire X axis label appears as a black box. The data series plots correctly. I can not seem to adjust the data label interval as with a single X data series plot. Doug - Is your X axis time + date, or time on a given date; I mean, does it go from midnight to midnight and you have multiple series corresponding to each day, or does it start at X o'clock on one day...