Calculating month 1 based on a date

[nengel]

Hi all:

I am trying to calculate two things - Identify an persons Month 1 based on when they were hired in a month - up to and including the 15th and then after the 15th

For example if the start date is <= 15th day of the month (ie March 14th) your hire month is March, if your start date is >15 (March 16) then your hire month is April. My calculation works except for when the start date is in December December 25) and the hire month should be January of the following year. Here is my formula - =IF(DAY(A3)<=15, MONTH(A3), IF(DAY(A3)>15,MONTH(A3)+1))

Once I have the hiring month calculated I need a different formula to indicate Training status - For example if the person's hire month <=4 months then their status is "Training" and if their hire month >4 months they are "Trained". I am really struggling with this one. =IF(EDATE(B8,4),"Training", IF(EDATE(B8,5), "Trained"))

Appreciate any help you can provide. Hoping that I am clear with what I am trying to do.

 

[nengel]

Screenshots attached

 

[jtakw]

Hi,

2 ways to show Hiring Month, B2 and C2

Book3.xlsx
	A	B	C	D	E
1	Start Date	Hire Month	Hire Month	Trained?	Using B Column
2	3/14/2022	3	Mar	Training	Training
3	3/16/2022	4	Apr	Training	Training
4	12/25/2021	1	Jan	Training	Trained
5	10/2/2021	10	Oct	Trained	Trained
6	11/10/2021	11	Nov	Training	Training
7	11/30/2021	12	Dec	Training	Training
Sheet1065

Cell Formulas
Range	Formula
B2:B7	B2	=MONTH(EDATE(A2,IF(DAY(A2)>15,1,0)))
C2:C7	C2	=TEXT(EDATE(A2,IF(DAY(A2)>15,1,0)),"mmm")
D2:D7	D2	=IF(EDATE(EOMONTH(EDATE(A2,IF(DAY(A2)>15,1,0)),-1)+1,4)>=EOMONTH(TODAY(),-1)+1,"Training","Trained")
E2:E7	E2	=IF(EDATE(DATE(YEAR(A2),B2,1),4)>=EOMONTH(TODAY(),-1)+1,"Training","Trained")

 

[jtakw]

Ignore E Column formula in my Post #3 above.
Use D Column formula for "Trained?"

 

[nengel]

Hi,

2 ways to show Hiring Month, B2 and C2

Book3.xlsx
	A	B	C	D	E
1	Start Date	Hire Month	Hire Month	Trained?	Using B Column
2	3/14/2022	3	Mar	Training	Training
3	3/16/2022	4	Apr	Training	Training
4	12/25/2021	1	Jan	Training	Trained
5	10/2/2021	10	Oct	Trained	Trained
6	11/10/2021	11	Nov	Training	Training
7	11/30/2021	12	Dec	Training	Training
Sheet1065

Cell Formulas
Range	Formula
B2:B7	B2	=MONTH(EDATE(A2,IF(DAY(A2)>15,1,0)))
C2:C7	C2	=TEXT(EDATE(A2,IF(DAY(A2)>15,1,0)),"mmm")
D2:D7	D2	=IF(EDATE(EOMONTH(EDATE(A2,IF(DAY(A2)>15,1,0)),-1)+1,4)>=EOMONTH(TODAY(),-1)+1,"Training","Trained")
E2:E7	E2	=IF(EDATE(DATE(YEAR(A2),B2,1),4)>=EOMONTH(TODAY(),-1)+1,"Training","Trained")

Thank you so much! I used B2 for the hiring month. Still trying to make the Training, Trained calc work.

 

[jtakw]

Thank you so much! I used B2 for the hiring month. Still trying to make the Training, Trained calc work.

Use my D2 formula for Training, as I mentioned, ignore E2 formula.

 

[nengel]

Ignore E Column formula in my Post #3 above.
Use D Column formula for "Trained?"

Got it! Thank you again! The formulas worked for both! Have a happy day!

 

[jtakw]

You're welcome, thanks for the feedback.