Calculate review date

[SGT Buckeye]

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?

 

[Guest]

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?

 

[David Biddulph]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[JMay]

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

 

[David Biddulph]

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.

 

[JMay]

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

 

[SGT Buckeye]

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.

 

[SGT Buckeye]

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.

 

[David Biddulph]

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