Date formulas needed

[David]

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

 

[Bob Phillips]

F4:
=IF(E4="","",IF(E4<=TODAY()+21,TODAY()+21,DATE(YEAR(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]

Bob Phillips wrote

F4:
=IF(E4="","",IF(E4<=TODAY()+21,TODAY()+21,DATE(YEAR(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(YEAR
(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.

 

[Bob Phillips]

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]

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

 

[Sandy Mann]

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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Bob Phillips]

2:23 or not, the OP said

E4 <= 21 days from today

that is E4<=TODAY()+21 in my reading

--

HTH

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

 

[Sandy Mann]

I see what you mean, however I had the benefit of seeing his reply to you
when he said:

"If E4 is more than 21 days ago"

which is surely saying the opposite.

--
Regards

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[David]

Sandy Mann wrote

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.

Actually, I took a whole other tack and came up with what I needed since
there was a scenario I wasn't originally taking into account. It required a
4th column. It took me all day to come up with the right combination.

E4 contains a preset date I type in. These are varied down column E.
This is crucial to my needs.

F4 adds 21 days
=IF($E4="","",$E4+21)

G4 does the first critical work
=IF(E4="","",IF(TODAY()<=DATE(YEAR(E4),MONTH(E4)+2,DAY(E4)),DATE(YEAR
(E4),MONTH(E4)+3,DAY(E4)),DATE(YEAR(F4),MONTH(F4)+6,DAY(F4))))

H4 finishes up
=IF(E4="","",IF(TODAY()<=DATE(YEAR(E4),MONTH(E4)+2,DAY(E4)),DATE(YEAR
(E4),MONTH(E4)+3,DAY(E4)),DATE(YEAR(G4),MONTH(G4)+6,DAY(G4))))

My next quest will be to try to eliminate the reference to TODAY(), as I
need things to remain static until I change the date in E4. I don't want
the values to change until I want/need them changed.

Bob at least let me stare at the proper DATE() syntax.

Of course if I could shorten things in G or H... Seems like a lot of
redundancy.

 

[David]

David wrote

My next quest will be to try to eliminate the reference to TODAY(), as I
need things to remain static until I change the date in E4. I don't want
the values to change until I want/need them changed.

Well, that was a stupid thing to say. Of course I want it tied to Today()!