If Formula , Adding Days to a Date

[Khalid A. Al-Otaibi]

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

 

[Lars-Åke Aspelin]

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

 

[מיכ×ל (מיקי) ×בידן]

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

 

[Khalid A. Al-Otaibi]

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, Pleas
-----------------------------------------------------------------------------------

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

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

 

[Rolf]

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