Three cells involved:
E4: date entered manually by user
F4: date next IPP due
G4: date next Review due

Need formulas for F4 and G4 to satisfy these conditions:
1) E4 is empty:
F4, G4 = ""
2) E4 <= 21 days from today:
F4 = E4 + 21 days, G4 = E4 + 90 days
3) Else:
F4 = E4 + 6 months, G4 = E4 + 1 year

--
David

F4:
=IF(E4="","",IF(E4<=TODAY()+21,TODAY()+21,DATE(YEA R(TODAY()),MONTH(TODAY())+
6,DAY(TODAY()))))

G4:=IF(E4="","",IF(E4<=TODAY()+21,TODAY()+90,DATE( YEAR(TODAY())+1,MONTH(TODA
Y()),DAY(TODAY()))))

be aware though adding 6 months to say 31st March + 6 months gives 1st Oct.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"David" wrote in message
...
Three cells involved:
E4: date entered manually by user
F4: date next IPP due
G4: date next Review due

Need formulas for F4 and G4 to satisfy these conditions:
1) E4 is empty:
F4, G4 = ""
2) E4 <= 21 days from today:
F4 = E4 + 21 days, G4 = E4 + 90 days
3) Else:
F4 = E4 + 6 months, G4 = E4 + 1 year

--
David

Bob Phillips wrote

F4:
=IF(E4="","",IF(E4<=TODAY()+21,TODAY()+21,DATE(YEA R(TODAY()),MONTH(TODA
Y())+ 6,DAY(TODAY()))))

G4:=IF(E4="","",IF(E4<=TODAY()+21,TODAY()+90,DATE( YEAR(TODAY())+1,MONTH
(TODAY()),DAY(TODAY()))))

Hmm... Guess I didn't explain my needs accurately.
Using your formulas with 4/4/05 in E4 I wound up with:
F4: 8/12/05
G4: 10/20/05

When what I wanted was:
F4: 10/4/05
G4: 4/4/06

On my own, I was able to at least get G4 to equal what I want with:
=IF(F4="","",IF(F4-E4<=21,DATE(YEAR(E4),MONTH(E4)+3,DAY(E4)),DATE(YEA R
(F4),MONTH(F4)+6,DAY(F4))))

But that was after manually entering 10/4/05 into F4

We got closer with 7/22/05 in E4:
Results:
F4: 8/12/05 as desired
G4: 10/20/05 when I wanted 10/22/05

As a matter of fact, no matter what the date in E4, results with your
formulas are ALWAYS F4:8/12/05 and G4:10/20/05

The more I think about a real life scenario, though, F4 will actually vary
and not depend on E4 after the initial 90-day period, so I'll be entering
its date manually after 90 days.

But the mystery remains why we were off by 2 days in the second example
above.

--
David

You are right, I got carried away with my TODAY()s.

I should have said

=IF(E4="","",IF(E4<=TODAY()+21,E4+21,DATE(YEAR(E4) ,MONTH(E4)+6,DAY(E4))))

and

=IF(E4="","",IF(E4<=TODAY()+21,E4+90,DATE(YEAR(E4) +1,MONTH(E4),DAY(E4))))

but it still gives different answers than you predict, as according to my
math, 4th April is <= TODAY + 21 days, so it is just E4 + 21 and 90.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"David" wrote in message
...
Bob Phillips wrote

F4:
=IF(E4="","",IF(E4<=TODAY()+21,TODAY()+21,DATE(YEA R(TODAY()),MONTH(TODA
Y())+ 6,DAY(TODAY()))))

G4:=IF(E4="","",IF(E4<=TODAY()+21,TODAY()+90,DATE( YEAR(TODAY())+1,MONTH
(TODAY()),DAY(TODAY()))))

Hmm... Guess I didn't explain my needs accurately.
Using your formulas with 4/4/05 in E4 I wound up with:
F4: 8/12/05
G4: 10/20/05

When what I wanted was:
F4: 10/4/05
G4: 4/4/06

On my own, I was able to at least get G4 to equal what I want with:
=IF(F4="","",IF(F4-E4<=21,DATE(YEAR(E4),MONTH(E4)+3,DAY(E4)),DATE(YEA R
(F4),MONTH(F4)+6,DAY(F4))))

But that was after manually entering 10/4/05 into F4

We got closer with 7/22/05 in E4:
Results:
F4: 8/12/05 as desired
G4: 10/20/05 when I wanted 10/22/05

As a matter of fact, no matter what the date in E4, results with your
formulas are ALWAYS F4:8/12/05 and G4:10/20/05

The more I think about a real life scenario, though, F4 will actually vary
and not depend on E4 after the initial 90-day period, so I'll be entering
its date manually after 90 days.

But the mystery remains why we were off by 2 days in the second example
above.

--
David

Bob Phillips wrote

You are right, I got carried away with my TODAY()s.

I should have said

=IF(E4="","",IF(E4<=TODAY()+21,E4+21,DATE(YEAR(E4) ,MONTH(E4)+6,DAY(E4))))

and

=IF(E4="","",IF(E4<=TODAY()+21,E4+90,DATE(YEAR(E4) +1,MONTH(E4),DAY(E4))))

but it still gives different answers than you predict, as according to my
math, 4th April is <= TODAY + 21 days, so it is just E4 + 21 and 90.


Ok, now I'm thoroughly confused by the results.
Let's try this again.
If E4 is more than 21 days ago, F4 should be 6 months from E4 and G4 should
be 1 year from E4
If E4 is less than 21 days ago, F4 should be 21 days from E4 and G4 should
be 90 days from E4

--
David

Bob was posting at 2:23am so that may account for it. Try changing the +21
in his formulas to -21 and it should give you what you are looking for.

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"David" wrote in message
...
Bob Phillips wrote

You are right, I got carried away with my TODAY()s.

I should have said

=IF(E4="","",IF(E4<=TODAY()+21,E4+21,DATE(YEAR(E4) ,MONTH(E4)+6,DAY(E4))))

and

=IF(E4="","",IF(E4<=TODAY()+21,E4+90,DATE(YEAR(E4) +1,MONTH(E4),DAY(E4))))

but it still gives different answers than you predict, as according to my
math, 4th April is <= TODAY + 21 days, so it is just E4 + 21 and 90.


Ok, now I'm thoroughly confused by the results.
Let's try this again.
If E4 is more than 21 days ago, F4 should be 6 months from E4 and G4
should
be 1 year from E4
If E4 is less than 21 days ago, F4 should be 21 days from E4 and G4 should
be 90 days from E4

--
David