Calculate review date
 

My employees receive bi-annual reviews. I know how to write a formula
to add six months to the hire date (in cell G2) to get the next review
date in cell G3. However, once the next review date is passed I want
the date in cell G3 to add another six months so that it always reflect
the next review date. I will then conditionally format the cell to
identify when I am two weeks or less from the next review date. I hope
this is clear enough. Any suggestions?

Calculate review date
 

You're clear enough, but that's difficult to do without resorting to VBA.

Are you recording the actual date of the review? If you are, then you could
put an IF() statement in G3 that would add 6 months to the Hire Date when
there is nothing in the "Review Performed" cell, but add 6 months to the date
in the Review Performed in G3 if there is a date there.

Let's say G4 held the date that an actual review was done, then G3 might
look something like this:
=IF(ISBLANK(G4),G2+180,G4+180)
if you're adding something other than 180 days in your formula, just
substitute that for the 180s in the formula above?

"SGT Buckeye" wrote:

My employees receive bi-annual reviews. I know how to write a formula
to add six months to the hire date (in cell G2) to get the next review
date in cell G3. However, once the next review date is passed I want
the date in cell G3 to add another six months so that it always reflect
the next review date. I will then conditionally format the cell to
identify when I am two weeks or less from the next review date. I hope
this is clear enough. Any suggestions?

Calculate review date
 

In G3, try:
=DATE(YEAR(TODAY()),MONTH(G2)+6*((TODAY()DATE(YEA R(TODAY()),MONTH(G2),DAY(G2)))+(TODAY()DATE(YEAR( TODAY()),MONTH(G2)+6,DAY(G2)))),DAY(G2))
--
David Biddulph

"SGT Buckeye" wrote in message
oups.com...
My employees receive bi-annual reviews. I know how to write a formula
to add six months to the hire date (in cell G2) to get the next review
date in cell G3. However, once the next review date is passed I want
the date in cell G3 to add another six months so that it always reflect
the next review date. I will then conditionally format the cell to
identify when I am two weeks or less from the next review date. I hope
this is clear enough. Any suggestions?

Calculate review date
 

If you are using EDATE() to get the 6 months, it would look like this:
=IF(ISBLANK(G4),EDATE(G2,6),EDATE(G4,6))



"JLatham" wrote:

You're clear enough, but that's difficult to do without resorting to VBA.

Are you recording the actual date of the review? If you are, then you could
put an IF() statement in G3 that would add 6 months to the Hire Date when
there is nothing in the "Review Performed" cell, but add 6 months to the date
in the Review Performed in G3 if there is a date there.

Let's say G4 held the date that an actual review was done, then G3 might
look something like this:
=IF(ISBLANK(G4),G2+180,G4+180)
if you're adding something other than 180 days in your formula, just
substitute that for the 180s in the formula above?

"SGT Buckeye" wrote:

My employees receive bi-annual reviews. I know how to write a formula
to add six months to the hire date (in cell G2) to get the next review
date in cell G3. However, once the next review date is passed I want
the date in cell G3 to add another six months so that it always reflect
the next review date. I will then conditionally format the cell to
identify when I am two weeks or less from the next review date. I hope
this is clear enough. Any suggestions?

Calculate review date
 

I rather like it!

Had me going for a minute, as I entered 1/1/06 as hire date and got 1/1/07
as next review due date. Then I realized that is correct since presumably
there was a review performed on 7/1/06 and with us being in December, then
1/1/07 is correct.

"David Biddulph" wrote:

In G3, try:
=DATE(YEAR(TODAY()),MONTH(G2)+6*((TODAY()DATE(YEA R(TODAY()),MONTH(G2),DAY(G2)))+(TODAY()DATE(YEAR( TODAY()),MONTH(G2)+6,DAY(G2)))),DAY(G2))
--
David Biddulph

"SGT Buckeye" wrote in message
oups.com...
My employees receive bi-annual reviews. I know how to write a formula
to add six months to the hire date (in cell G2) to get the next review
date in cell G3. However, once the next review date is passed I want
the date in cell G3 to add another six months so that it always reflect
the next review date. I will then conditionally format the cell to
identify when I am two weeks or less from the next review date. I hope
this is clear enough. Any suggestions?

Calculate review date
 

Perhaps a little simpler....

=DATE(YEAR(G2),MONTH(G2)+CEILING(DATEDIF(G2,TODAY( )-1,"m")+1,6),DAY(G2))

or using EDATE

=EDATE(G2,CEILING(DATEDIF(G2,TODAY()-1,"m")+1,6))

Note that the first of these gives the same result as David's suggestion but
the EDATE version gives different results for hire dates like 31st March,
31st May, 31st Octber etc. i.e. the first formula will give some 31st October
hire date reviews on 1st May whereas the EDATE version will give 30th
April......

"JLatham" wrote:

I rather like it!

Had me going for a minute, as I entered 1/1/06 as hire date and got 1/1/07
as next review due date. Then I realized that is correct since presumably
there was a review performed on 7/1/06 and with us being in December, then
1/1/07 is correct.

"David Biddulph" wrote:

In G3, try:
=DATE(YEAR(TODAY()),MONTH(G2)+6*((TODAY()DATE(YEA R(TODAY()),MONTH(G2),DAY(G2)))+(TODAY()DATE(YEAR( TODAY()),MONTH(G2)+6,DAY(G2)))),DAY(G2))
--
David Biddulph

"SGT Buckeye" wrote in message
oups.com...
My employees receive bi-annual reviews. I know how to write a formula
to add six months to the hire date (in cell G2) to get the next review
date in cell G3. However, once the next review date is passed I want
the date in cell G3 to add another six months so that it always reflect
the next review date. I will then conditionally format the cell to
identify when I am two weeks or less from the next review date. I hope
this is clear enough. Any suggestions?

Calculate review date
 

In G3 enter:
=IF(EDATE(G2,6)<TODAY(),EDATE(G2,12),EDATE(G2,6))

The must have the Analysis ToolPak (Add-In) installed
In order to access the EDate() function.

HTH


"SGT Buckeye" wrote in message
oups.com:

My employees receive bi-annual reviews. I know how to write a formula
to add six months to the hire date (in cell G2) to get the next review
date in cell G3. However, once the next review date is passed I want
the date in cell G3 to add another six months so that it always reflect
the next review date. I will then conditionally format the cell to
identify when I am two weeks or less from the next review date. I hope
this is clear enough. Any suggestions?

Calculate review date
 

My understanding is that the OP would wish the formula to continue to update
the review date every 6 months, so someone hired in 2004, for example,
should still have their next review date shown? That's what my formula was
trying to do, and similarly the two shorter options from daddylonglegs.
--
David Biddulph

"JMay" wrote in message
...
In G3 enter:
=IF(EDATE(G2,6)<TODAY(),EDATE(G2,12),EDATE(G2,6))

The must have the Analysis ToolPak (Add-In) installed
In order to access the EDate() function.

"SGT Buckeye" wrote in message
oups.com:

My employees receive bi-annual reviews. I know how to write a formula
to add six months to the hire date (in cell G2) to get the next review
date in cell G3. However, once the next review date is passed I want
the date in cell G3 to add another six months so that it always reflect
the next review date. I will then conditionally format the cell to
identify when I am two weeks or less from the next review date. I hope
this is clear enough. Any suggestions?

Calculate review date
 

David, I was going by this comment the OP made...

"once the next review date is passed"

I want the date in cell G3 to add another six months

Anyway,
Thanks

"David Biddulph" wrote in message
:

My understanding is that the OP would wish the formula to continue to update
the review date every 6 months, so someone hired in 2004, for example,
should still have their next review date shown? That's what my formula was
trying to do, and similarly the two shorter options from daddylonglegs.
--
David Biddulph

"JMay" wrote in message
...
In G3 enter:
=IF(EDATE(G2,6)<TODAY(),EDATE(G2,12),EDATE(G2,6))

The must have the Analysis ToolPak (Add-In) installed
In order to access the EDate() function.

"SGT Buckeye" wrote in message
oups.com:

My employees receive bi-annual reviews. I know how to write a formula
to add six months to the hire date (in cell G2) to get the next review
date in cell G3. However, once the next review date is passed I want
the date in cell G3 to add another six months so that it always reflect
the next review date. I will then conditionally format the cell to
identify when I am two weeks or less from the next review date. I hope
this is clear enough. Any suggestions?

Calculate review date
 

Thanks to everyone who has posted in an effort to help me with this
problem. I will start working on this and see if any of your
suggestions works for me. Until then, thanks again.
JMay wrote:
David, I was going by this comment the OP made...

"once the next review date is passed"

I want the date in cell G3 to add another six months

Anyway,
Thanks

"David Biddulph" wrote in message
:

My understanding is that the OP would wish the formula to continue to update
the review date every 6 months, so someone hired in 2004, for example,
should still have their next review date shown? That's what my formula was
trying to do, and similarly the two shorter options from daddylonglegs.
--
David Biddulph

"JMay" wrote in message
...
In G3 enter:
=IF(EDATE(G2,6)<TODAY(),EDATE(G2,12),EDATE(G2,6))

The must have the Analysis ToolPak (Add-In) installed
In order to access the EDate() function.

"SGT Buckeye" wrote in message
oups.com:

My employees receive bi-annual reviews. I know how to write a formula
to add six months to the hire date (in cell G2) to get the next review
date in cell G3. However, once the next review date is passed I want
the date in cell G3 to add another six months so that it always reflect
the next review date. I will then conditionally format the cell to
identify when I am two weeks or less from the next review date. I hope
this is clear enough. Any suggestions?

Calculate review date
 

Thanks for taking the time to review my problem and send this post. It
works perfectly. I'd like to think that I know a lot about writing
formulas but I have no idea what the ceiling part of your formula
means. Could you possibly explain it to me so that I know for future
reference.
daddylonglegs wrote:
Perhaps a little simpler....

=DATE(YEAR(G2),MONTH(G2)+CEILING(DATEDIF(G2,TODAY( )-1,"m")+1,6),DAY(G2))

or using EDATE

=EDATE(G2,CEILING(DATEDIF(G2,TODAY()-1,"m")+1,6))

Note that the first of these gives the same result as David's suggestion but
the EDATE version gives different results for hire dates like 31st March,
31st May, 31st Octber etc. i.e. the first formula will give some 31st October
hire date reviews on 1st May whereas the EDATE version will give 30th
April......

"JLatham" wrote:

I rather like it!

Had me going for a minute, as I entered 1/1/06 as hire date and got 1/1/07
as next review due date. Then I realized that is correct since presumably
there was a review performed on 7/1/06 and with us being in December, then
1/1/07 is correct.

"David Biddulph" wrote:

In G3, try:
=DATE(YEAR(TODAY()),MONTH(G2)+6*((TODAY()DATE(YEA R(TODAY()),MONTH(G2),DAY(G2)))+(TODAY()DATE(YEAR( TODAY()),MONTH(G2)+6,DAY(G2)))),DAY(G2))
--
David Biddulph

"SGT Buckeye" wrote in message
oups.com...
My employees receive bi-annual reviews. I know how to write a formula
to add six months to the hire date (in cell G2) to get the next review
date in cell G3. However, once the next review date is passed I want
the date in cell G3 to add another six months so that it always reflect
the next review date. I will then conditionally format the cell to
identify when I am two weeks or less from the next review date. I hope
this is clear enough. Any suggestions?

Calculate review date
 

If it's the CEILING function which is confusing you, type CEILING into
Excel's help, similarly for nearly every other function. If what's
confusing you is DATEDIF, that's one which Microsoft in their infinite
wisdom have hidden from Help, but you can find details at
http://www.cpearson.com/excel/datedif.htm
--
David Biddulph

"SGT Buckeye" wrote in message
oups.com...
Thanks for taking the time to review my problem and send this post. It
works perfectly. I'd like to think that I know a lot about writing
formulas but I have no idea what the ceiling part of your formula
means. Could you possibly explain it to me so that I know for future
reference.

daddylonglegs wrote:
Perhaps a little simpler....

=DATE(YEAR(G2),MONTH(G2)+CEILING(DATEDIF(G2,TODAY( )-1,"m")+1,6),DAY(G2))

or using EDATE

=EDATE(G2,CEILING(DATEDIF(G2,TODAY()-1,"m")+1,6))

Note that the first of these gives the same result as David's suggestion
but
the EDATE version gives different results for hire dates like 31st March,
31st May, 31st Octber etc. i.e. the first formula will give some 31st
October
hire date reviews on 1st May whereas the EDATE version will give 30th
April......

"JLatham" wrote:

I rather like it!

Had me going for a minute, as I entered 1/1/06 as hire date and got
1/1/07
as next review due date. Then I realized that is correct since
presumably
there was a review performed on 7/1/06 and with us being in December,
then
1/1/07 is correct.

"David Biddulph" wrote:

In G3, try:
=DATE(YEAR(TODAY()),MONTH(G2)+6*((TODAY()DATE(YEA R(TODAY()),MONTH(G2),DAY(G2)))+(TODAY()DATE(YEAR( TODAY()),MONTH(G2)+6,DAY(G2)))),DAY(G2))

"SGT Buckeye" wrote in message
oups.com...
My employees receive bi-annual reviews. I know how to write a
formula
to add six months to the hire date (in cell G2) to get the next
review
date in cell G3. However, once the next review date is passed I
want
the date in cell G3 to add another six months so that it always
reflect
the next review date. I will then conditionally format the cell
to
identify when I am two weeks or less from the next review date. I
hope
this is clear enough. Any suggestions?