Calculate next employee review date from hire date

[Albie27]

Hello all - I have a spreadsheet that lists employees by date of hire. We have a review cycle that occurs at 90 days, 180 days, 365 days, and then thereafter every 6 months. I would like to create a formula to show what the employee’s next upcoming review date is. Any help is much appreciated!!

 

[gordsky]

Hi, Im sure there must be a shorter version and if I work it out I'll post it but this should do what you need. It assumes the Hire Date is in A1

=IF(A1+90>TODAY(),A1+90,IF(A1+180>TODAY(),A1+180,IF(A1+365>TODAY(),A1+365,IF(DATEVALUE(DAY(A1)&"/"&MONTH(A1)&"/"&(YEAR(TODAY())+1))-(365/2)>TODAY(),DATEVALUE(DAY(A1)&"/"&MONTH(A1)&"/"&(YEAR(TODAY())+1))-(365/2),DATEVALUE(DAY(A1)&"/"&MONTH(A1)&"/"&(YEAR(TODAY())+1))))))

 

[Fluff]

Hi & welcome to MrExcel.
Another option

=LET(d,TODAY()-A2,IF(d<90,A2+90,IF(d<180,A2+180,IF(d<365,A2+365,EDATE(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2)),6)))))

 

[Fluff]

Ignore the formula in post#3 it doesn't work properly.

 

[Fluff]

Revised formula

=LET(d,TODAY(),l,d-A2,IF(l<90,A2+90,IF(l<180,A2+180,IF(l<365,A2+365,EDATE(DATE(YEAR(d),MONTH(A2),DAY(A2)),IF(MONTH(A2)<MONTH(d),6,0))))))

 

[jtakw]

Hi,

I think this should also work:

Book3.xlsx
	A	B
1	Hire Date	Next Review Date
2	1/1/2022	4/1/2022
3	10/1/2021	3/30/2022
4	6/15/2021	6/15/2022
5	5/31/2021	5/31/2022
6	3/8/2021	9/8/2022
7	1/1/2021	7/1/2022
8	12/2/2020	6/2/2022
9	7/20/2020	1/20/2022
Sheet7

Cell Formulas
Range	Formula
B2:B9	B2	=IFERROR(A2+CHOOSE(INT((TODAY()-A2)/90)+1,90,180,365,365),EDATE(A2+365,6))

 

[jtakw]

Same here, ignore my formula in Post # 6, need adjustment.

 

[Fluff]

@jtakw I think your formula is right & mine is still wrong.

 

[jtakw]

@Fluff , no, my formula above is flawed, see row 6 and row 9 results.

I think this modified version should work correctly, tested somewhat extensively, would OP Please let us know if it works on his/her actual data.

Book3.xlsx
	A	B
1	Hire Date	Next Review Date
2	1/1/2022	4/1/2022
3	10/1/2021	3/30/2022
4	6/15/2021	6/15/2022
5	5/31/2021	5/31/2022
6	3/8/2021	3/8/2022
7	1/1/2021	7/1/2022
8	12/2/2020	6/2/2022
9	7/20/2020	7/20/2022
10	1/1/2015	7/1/2022
11	7/17/1986	7/17/2022
12	8/16/1986	8/16/2022
13	7/31/1986	7/31/2022
14	8/1/1986	8/1/2022
15	3/7/2021	9/7/2022
Sheet7

Cell Formulas
Range	Formula
B2:B15	B2	=IFERROR(A2+CHOOSE(INT((TODAY()-A2)/90)+1,90,180,365,365),EDATE(A2,IF(TODAY()-A2<=365,12,CEILING(DATEDIF(A2-30,TODAY(),"m"),6))))

 

[Fluff]

my formula above is flawed, see row 6 and row 9 results.

I see what you mean.