Assistance Needed with Excel Formula

[JanaP73]

Hi,

I'm struggling to find an Excel formula for the following scenario:

- If the enrollment date is greater than 6 months but less than 2 years and 1 month, the output should be "OK."
- If the enrollment date is greater than 2 years and 1 month but less than 4 years, the output should be "No."
- If the enrollment date is greater than 4 years, the output should be "Perfect."

Could you help me with this? Thank you!

 

[kvsrinivasamurthy]

Enrollment date is compared to today's date or anything else.
A small data with expected result in XL2BB format makes easy to solve the problem. Pl post data.

 

[Georgiboy]

Welcome to the forum JanaP73,

Maybe the DATEDIF function will get you started, it returns the difference between two dates in a unit of your choice, months in your example. In the below example it compares dates to todays date and returns that difference in months as a whole number.

Book1
	A	B	C
1	Enrollment Date	Difference to today in months
2	01/01/2010	182
3	01/01/2024	14
4	01/01/2023	26
5	28/01/2025	2
6
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=DATEDIF(A2,TODAY(),"M")

 

[JanaP73]

Enrollment date is compared to today's date or anything else.
A small data with expected result in XL2BB format makes easy to solve the problem. Pl post d

 

[JanaP73]

Enrollment date is compared to today's date or anything else.
A small data with expected result in XL2BB format makes easy to solve the problem. Pl post data.

I apologise for not mentioning earlier that the enrollment date is compared to today's date
Thank you

Welcome to the forum JanaP73,

Maybe the DATEDIF function will get you started, it returns the difference between two dates in a unit of your choice, months in your example. In the below example it compares dates to todays date and returns that difference in months as a whole number.

Book1
	A	B	C
1	Enrollment Date	Difference to today in months
2	01/01/2010	182
3	01/01/2024	14
4	01/01/2023	26
5	28/01/2025	2
6
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=DATEDIF(A2,TODAY(),"M")

 

[JanaP73]

Hi Georgiboy,

Thank you for your suggestion. I tried that, but it is still not working. I have uploaded the spreadsheet I'm working on. Please see if you can fix it. Thank you.

 

[Georgiboy]

I am confused now, it looks like you are trying to do something different from the original request?

Are you trying to create a text string with years, months and days different between a date in column A and todays date?

 

[kvsrinivasamurthy]

Less 2yrs 1 mth is "OK".
2 yrs, 1 mth, days 10 will be what?

 

[Georgiboy]

See if the below helps:

matches.xlsx
	A	B	C	D	E
1	Enrollment Date	Probation End Date	Year of Service	Check
2
3	21/05/2018	21/11/2018	6 years, 10 months, 7 days	Perfect
4	17/08/2020	17/02/2021	4 years, 7 months, 11 days	Perfect
5	24/06/2019	24/12/2019	5 years, 9 months, 4 days	Perfect
6	06/06/2022	06/12/2022	2 years, 9 months, 22 days	No
7	22/08/2022	22/02/2023	2 years, 7 months, 6 days	No
8	27/10/2022	27/04/2023	2 years, 5 months, 1 days	No
9	04/06/2017	04/12/2017	7 years, 9 months, 24 days	Perfect
10	01/09/2007	01/03/2008	17 years, 6 months, 27 days	Perfect
11	01/09/2020	01/03/2021	4 years, 6 months, 27 days	Perfect
12	04/01/2025	04/07/2025	0 years, 2 months, 24 days
13	11/08/2014	11/02/2015	10 years, 7 months, 17 days	Perfect
14	02/11/2015	02/05/2016	9 years, 4 months, 26 days	Perfect
15	06/11/2017	06/05/2018	7 years, 4 months, 22 days	Perfect
16	08/11/2021	08/05/2022	3 years, 4 months, 20 days	No
17	25/11/2021	25/05/2022	3 years, 4 months, 3 days	No
18	01/01/2024	01/07/2024	1 years, 2 months, 27 days	OK
19
Sheet1

Cell Formulas
Range	Formula
B3:B18	B3	=EDATE(A3,6)
C3:C18	C3	=LET(d,A3,t,TODAY(),DATEDIF(d,t,"Y")&" years, "&DATEDIF(d,t,"YM")&" months, "&DATEDIF(d,t,"MD")&" days")
D3:D18	D3	=LET(dd,DATEDIF(A3,TODAY(),"M"),IF(dd>=48,"Perfect",IF(AND(dd<48,dd>25),"No",IF(AND(dd<=25,dd>=6),"OK",""))))

 

[kvsrinivasamurthy]

Timesheet.xlsm
	A	B	C	D
1	Date	Formula	Yrs,Mths,Days
2	29-09-2024		0y,5m,27d
3	27-09-2024	OK	0y,6m,1d
4	01-10-2022	NO	2y,5m,27d
5	29-03-2021	NO	3y,11m,27d
6	30-03-2021	NO	3y,11m,26d
7	04-03-2021	PERFECT	4y,0m,24d
8
Sheet2

Cell Formulas
Range	Formula
B2:B7	B2	=IF(A2="","",IF((DATEDIF(A2,TODAY(),"Y")*12+ DATEDIF(A2,TODAY(),"YM")+(DATEDIF(A2,TODAY(),"MD")>0))<=6,"",IF((DATEDIF(A2,TODAY(),"Y")*12+ DATEDIF(A2,TODAY(),"YM")+(DATEDIF(A2,TODAY(),"MD")>0))<=25,"OK",IF((DATEDIF(A2,TODAY(),"Y")*12+ DATEDIF(A2,TODAY(),"YM")+ (DATEDIF(A2,TODAY(),"MD")>0))<=48,"NO","PERFECT"))))
C2:C7	C2	=+DATEDIF(A2,TODAY(),"Y")&"y,"& DATEDIF(A2,TODAY(),"YM")&"m,"& DATEDIF(A2,TODAY(),"MD")&"d"

For other than 365 version.
In B2 copied down

=IF(A2="","",IF((DATEDIF(A2,TODAY(),"Y")*12+ DATEDIF(A2,TODAY(),"YM")+(DATEDIF(A2,TODAY(),"MD")>0))<=6,"",IF((DATEDIF(A2,TODAY(),"Y")*12+ DATEDIF(A2,TODAY(),"YM")+(DATEDIF(A2,TODAY(),"MD")>0))<=25,"OK",IF((DATEDIF(A2,TODAY(),"Y")*12+ DATEDIF(A2,TODAY(),"YM")+ (DATEDIF(A2,TODAY(),"MD")>0))<=48,"NO","PERFECT"))))

In C2 copied down

=DATEDIF(A2,TODAY(),"Y")&"y,"& DATEDIF(A2,TODAY(),"YM")&"m,"& DATEDIF(A2,TODAY(),"MD")&"d"

For 365 version.
In B2

=LET(a,A2:A7,yrm,DATEDIF(a,TODAY(),"Y")*12,mns,DATEDIF(a,TODAY(),"YM"),dys,DATEDIF(a,TODAY(),"MD")>0,MAP(a,yrm,mns,dys,LAMBDA(xa,xy,xm,xd,IF(xa="","",IF((xy+xm+xd)<=6,"",IF((xy+xm+xd)<=25,"OK",IF((xy+xm+xd)<=48,"NO","PERFECT")))))))

In C2

=LET(a,A2:A7,DATEDIF(a,TODAY(),"Y")&"y,"& DATEDIF(a,TODAY(),"YM")&"m,"& DATEDIF(a,TODAY(),"MD")&"d")