Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if cell has date value how to calculate other cell values | Excel Discussion (Misc queries) | |||
Mileage Claim Formula | New Users to Excel | |||
Auto calculate for date + days forward to yield new date | Excel Worksheet Functions | |||
How to calculate "number of months" between two given date? | Excel Worksheet Functions | |||
formula to calculate age using birth date and current date | Excel Worksheet Functions |