IF and DATE Formula Help PLEASE

[Diane1477]

I am trying to calculate review dates for over 500 people. I want to create
a sheet that calculates reviews in 6 months for new hires and every 2 years
for existing staff. My example data is below and I'm to do the following
calculations in Column C:

Hire Date (A) Review Date (B) Next Review Due (C)
1/1/03 1/1/05 Formula here would = 1/1/07
(using
=DATE(YEAR(B2)+2,MONTH(B2),DAY(B2))
1/1/07 Formula here would = 7/1/07

(using=DATE(YEAR(A3),MONTH(A3)+6,DAY(A3))
2/5/04 2/5/06 Formula here would = 2/5/08
3/8/07 Formula here would = 9/8/07

I am trying to create an iF statement that says IF Column B has a date then
do the calculation =DATE(YEAR(B2)+2,MONTH(B2),DAY(B2)), but IF Column B is
blank do the calculation =DATE(YEAR(A2),MONTH(A2)+6,DAY(A2))

I can not get it to work. Is it possible and if so, what is the write
formula??

 

[Roger Whitehead]

Hi Diane,

Try:
=IF(ISBLANK(B2),DATE(YEAR(A2),MONTH(A2)+6,DAY(A2)),DATE(YEAR(B2)+2,MONTH(B2),DAY(B2)))

Control input to colums A&B with Data Validation, using a date range. That
should stop confusing data being places there.

--

 

[Darren Bartrup]

Should work:

=IF(B2<>"",DATE(YEAR(B2)+2,MONTH(B2),DAY(B2)),DATE(YEAR(A2),MONTH(A2)+6,DAY(A2)))

 

[Pete_UK]

Try it this way round:

=IF(ISBLANK(B2),DATE(YEAR(A2),MONTH(A2)+6,DAY(A2)),DATE(YEAR(B2)+2,MONTH(B2),DAY(B2)))

Hope this helps.

Pete

 

[Darren Bartrup]

I always forget ISBLANK exists

 

[Diane1477]

THANK YOU VERY MUCH. IT WORKED PERFECTLY!

Try it this way round:

=IF(ISBLANK(B2),DATE(YEAR(A2),MONTH(A2)+6,DAY(A2)),DATE(YEAR(B2)+2,MONTH(B2),DAY(B2)))

Hope this helps.

Pete

 

[Pete_UK]

Glad to hear it, Diane - thanks for feeding back.

Pete