IF and DATE Formula Help PLEASE

D

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??
 
R

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.

--
 
D

Darren Bartrup

Should work:

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

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
 
D

Diane1477

THANK YOU VERY MUCH. IT WORKED PERFECTLY!

Pete_UK said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top