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