help with formula

[Richard]

If B16 (which is a date) is between January 1st. and June 30th. then 3
If B16 (which is a date) is between July 1st. and September 30th then 2
If B16 (which is a date) is between October 1st. and December 31st. then 0
If B16 > 5 years then 4
Thanks in advance for any help!!

 

[Franz Verga]

If B16 (which is a date) is between January 1st. and June 30th. then 3
If B16 (which is a date) is between July 1st. and September 30th then 2
If B16 (which is a date) is between October 1st. and December 31st. then 0
If B16 > 5 years then 4
Thanks in advance for any help!!

I don't understand the last if. What do you mean with

If B16 > 5 years then 4 ?

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Dave F]

=if(and(b16>date(2006,01,01),b16<2006,06,30),3,if(and(b16>date(july
1st),b16<(September 30th),2,etc.

 

[Richard]

B16 is formatted as a date and never changes, Example 7/31/06, If TODAYS
DATE is 1/1/2011 or greater then 4

 

[Franz Verga]

B16 is formatted as a date and never changes, Example 7/31/06, If
TODAYS DATE is 1/1/2011 or greater then 4

Hi Richard,
try this:

=IF(YEAR(TODAY())-YEAR(B16)>5,4,IF(AND(B16>=DATE(YEAR(B16),1,1),B16<=DATE(YEAR(B16),6,30)),3,IF(AND(B16>=DATE(YEAR(B16),7,1),B16<=DATE(YEAR(B16),9,30)),2,IF(AND(B16>=DATE(YEAR(B16),10,1),B16<=DATE(YEAR(B16),12,31)),0,""))))


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[MyVeryOwnSelf]

If B16 (which is a date) is between January 1st. and June 30th. then 3

If B16 (which is a date) is between July 1st. and September 30th then
2 If B16 (which is a date) is between October 1st. and December 31st.
then 0 If B16 > 5 years then 4

Maybe this gets what you want:

=IF(B16>DATE(YEAR(TODAY())+5,MONTH(TODAY()),DAY(TODAY())),4,
2*(MONTH(B16)<10)+(MONTH(B16)<7))