Issue with changing a condition based on a date

[nengel]

Hello:

I am having an issue with a formula that was built to address the following scenario:

When an employee is hired, if they are hired between the 1-15th of the month, that month is the month of hire. If an employee is hired after the 15th then the month of hire is the following month.

If an employee is in month 1-4, the are considered a ramping employee, if they are in month 5 they are considered a ramped employee. I am having an issue with the status changing to ramped once the hit month 5. Here is what I have. You can see that for hires that have a ramped date of May, the status hasn't changed to ramped.

I'd appreciate any insight into how to resolve.

Calculating hire month.xlsx
	A	B	C	D	E
1	Start Date mm/dd/year	Hiring Month	Hire Month	Job Status	Ramped Date
2	3/14/2022	3	Mar	Ramping	Jul/2022
3	3/16/2022	4	Apr	Ramping	Aug/2022
4	12/25/2021	1	Jan	Ramping	May/2022
5	10/2/2021	10	Oct	Ramped	Feb/2022
6	10/11/2021	10	Oct	Ramped	Feb/2022
7	4/14/2022	4	Apr	Ramping	Aug/2022
8	12/15/2021	12	Dec	Ramped	Apr/2022
9	11/16/2021	12	Dec	Ramped	Apr/2022
10	1/8/2022	1	Jan	Ramping	May/2022
11	4/4/2022	4	Apr	Ramping	Aug/2022
Sheet1

Cell Formulas
Range	Formula
B2:B11	B2	=MONTH(EDATE(A2,IF(DAY(A2)>15,1,0)))
C2:C11	C2	=TEXT(EDATE(A2,IF(DAY(A2)>15,1,0)),"mmm")
D2:D11	D2	=IF(EDATE(EOMONTH(EDATE(A2,IF(DAY(A2)>15,1,0)),-1)+1,4)>=EOMONTH(TODAY(),-1)+1,"Ramping","Ramped")
E2:E11	E2	=EDATE(EOMONTH(EDATE(A2,IF(DAY(A2)>15,1,0)),-1)+1,4)

 

[C Moore]

Do you need the Hiring Month or can you revise your formulas and format as needed?

scratchbook excel testing.xlsm
	A	B	C	D	E
14	Start Date mm/dd/year	Hiring Month	Hire Month	Job Status	Ramped Date
15	3/14/2022	3/1/2022	Mar	Ramping	8/1/2022
16	3/16/2022	4/1/2022	Apr	Ramping	9/1/2022
17	12/25/2021	1/1/2022	Jan	Ramping	6/1/2022
18	10/2/2021	10/1/2021	Oct	Ramped	3/1/2022
19	10/11/2021	10/1/2021	Oct	Ramped	3/1/2022
20	4/14/2022	4/1/2022	Apr	Ramping	9/1/2022
21	12/15/2021	12/1/2021	Dec	Ramped	5/1/2022
22	11/16/2021	12/1/2021	Dec	Ramped	5/1/2022
23	1/8/2022	1/1/2022	Jan	Ramping	6/1/2022
24	4/4/2022	4/1/2022	Apr	Ramping	9/1/2022
Sheet22

Cell Formulas
Range	Formula
B15:B24	B15	=EOMONTH(EDATE(A15,IF(DAY(A15)>15,1,0)),-1)+1
C15:C24	C15	=TEXT(B15,"mmm")
D15:D24	D15	=IF(TODAY()>=E15,"Ramped","Ramping")
E15:E24	E15	=EDATE(B15,5)

 

[nengel]

Do you need the Hiring Month or can you revise your formulas and format as needed?

scratchbook excel testing.xlsm
	A	B	C	D	E
14	Start Date mm/dd/year	Hiring Month	Hire Month	Job Status	Ramped Date
15	3/14/2022	3/1/2022	Mar	Ramping	8/1/2022
16	3/16/2022	4/1/2022	Apr	Ramping	9/1/2022
17	12/25/2021	1/1/2022	Jan	Ramping	6/1/2022
18	10/2/2021	10/1/2021	Oct	Ramped	3/1/2022
19	10/11/2021	10/1/2021	Oct	Ramped	3/1/2022
20	4/14/2022	4/1/2022	Apr	Ramping	9/1/2022
21	12/15/2021	12/1/2021	Dec	Ramped	5/1/2022
22	11/16/2021	12/1/2021	Dec	Ramped	5/1/2022
23	1/8/2022	1/1/2022	Jan	Ramping	6/1/2022
24	4/4/2022	4/1/2022	Apr	Ramping	9/1/2022
Sheet22

Cell Formulas
Range	Formula
B15:B24	B15	=EOMONTH(EDATE(A15,IF(DAY(A15)>15,1,0)),-1)+1
C15:C24	C15	=TEXT(B15,"mmm")
D15:D24	D15	=IF(TODAY()>=E15,"Ramped","Ramping")
E15:E24	E15	=EDATE(B15,5)

I need the hiring month mostly to define what month 1 is (based if they are hired in the first 15 days or last 15ish days), so I can project the month they will be ramped (which is month 5) + change their Job status from ramping to ramped when they hit the ramped month.

 

[C Moore]

So my solution of the effective hire date for ramping works as shown?

 

[nengel]

Hello, no the hiring month is dependant on this condition:

Example: If employee was hired May 5th, then hiring month is May and the month that they would be considered ramped in September (4 month ramping period is completed). If employee is hired May 16th then hiring month is June and they would be considered ramped in October.

The original formulas are working with the exception of the Job Status column. The status should change to Ramped once they reach the ramped date has been met. So in your table, row 17th - the hiring month is correct but the Ramped Date should be May and the status should be ramped and in row 23 the ramped month should be May and the status should be Ramped.

I can't figure out why my original formulas are not working consistently because it works in row 5 and 6 but not in row 4 or 10.

 

[C Moore]

Remove the second equal sign in column D

 

[nengel]

Thank you! That fixed it! I am so happy!