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

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.

--
---
HTH
Roger
Shaftesbury (UK)
(Excel 2003, Win XP/SP2)

"Diane1477" wrote in message
...
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??

Should work:

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

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

On Oct 3, 4:01 pm, Diane1477
wrote:
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??

I always forget ISBLANK exists :p

THANK YOU VERY MUCH. IT WORKED PERFECTLY!

"Pete_UK" wrote:

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

On Oct 3, 4:01 pm, Diane1477
wrote:
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??

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

Pete

On Oct 3, 5:18 pm, Diane1477
wrote:
THANK YOU VERY MUCH. IT WORKED PERFECTLY!



"Pete_UK" wrote:
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

On Oct 3, 4:01 pm, Diane1477
wrote:
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??- Hide quoted text -

- Show quoted text -