Date formula

[coolkat999]

Hi

I need some help writing a formula.

I have a table showing dates of birth for employees. I want to be able to show a "yes" in the table if the employee reaches the minimum age in months under each of the years. So for example, the first employee with a dob of 1/2/1960 should only have Yes showing under the date range 1/1/2021 - 31/12/2021 as they will have met the minimum age requirement of 738 months.

However, I only need the "yes" to show once (ie the earliest possible occasion)

Could someone help me write the formula?


[TABLE="width: 695"]
<colgroup><col width="87" style="width: 65pt;"><col width="173" style="width: 130pt;"><col width="87" span="5" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="width: 87"][/TD]
[TD="class: xl65, width: 173"]From[/TD]
[TD="class: xl69, width: 87, align: right"]01/01/2019[/TD]
[TD="class: xl69, width: 87, align: right"]01/01/2020[/TD]
[TD="class: xl69, width: 87, align: right"]01/01/2021[/TD]
[TD="class: xl69, width: 87, align: right"]01/01/2022[/TD]
[TD="class: xl70, width: 87, align: right"]01/01/2023[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl66"]To[/TD]
[TD="class: xl67, align: right"]31/12/2019[/TD]
[TD="class: xl67, align: right"]31/12/2020[/TD]
[TD="class: xl67, align: right"]31/12/2021[/TD]
[TD="class: xl67, align: right"]31/12/2022[/TD]
[TD="class: xl68, align: right"]31/12/2023[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl66"]Required age (months)[/TD]
[TD="class: xl71, align: right"]736[/TD]
[TD="class: xl71, align: right"]737[/TD]
[TD="class: xl71, align: right"]738[/TD]
[TD="class: xl71, align: right"]739[/TD]
[TD="class: xl72, align: right"]740[/TD]
[/TR]
[TR]
[TD]DOB[/TD]
[TD="class: xl73, align: right"]01/02/1960[/TD]
[TD="class: xl74"] [/TD]
[TD="class: xl74"] [/TD]
[TD="class: xl74"]Yes[/TD]
[TD="class: xl74"] [/TD]
[TD="class: xl74"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl73, align: right"]14/08/1958[/TD]
[TD="class: xl75"]Yes[/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl75"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl73, align: right"]09/08/1964[/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl75"] [/TD]
[/TR]
</tbody>[/TABLE]
Many thanks

Will

 

[jtakw]

Hi,

Does this do what you need:

Book1
	A	B	C	D	E	F	G
1		From	1/1/2019	1/1/2020	1/1/2021	1/1/2022	1/1/2023
2		To	12/31/2019	12/31/2020	12/31/2021	12/31/2022	12/31/2023
3		Required age (months)	736	737	738	739	740
4	DOB	2/1/1960			Yes
5		8/14/1958	Yes
6		8/9/1964
Sheet432

Cell Formulas
Range	Formula
C4	=IF(AND(YEAR(EDATE($B4,C$3))=YEAR(C$2),COUNTIF($B4:B4,"Yes")=0),"Yes","")

Formula copied down and across.

 

[Kamolga]

Hi, I would use datedif formula (with 'from' in a1 and considering date difference in row 2 in months)
<b$3,countif($a4:a4,"yes")>

DATEDIF($A4,B$2,"m")

so in b4:

</b$3,countif($a4:a4,"yes")>

[/URL][/IMG]

 

[Kamolga]

.

 

[coolkat999]

many thanks jtakw this works a treat

Hi,

Does this do what you need:

	A	B	C	D	E	F	G
From
To
Required age (months)
DOB			Yes
Yes

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1/1/2019[/TD]
[TD="align: right"]1/1/2020[/TD]
[TD="align: right"]1/1/2021[/TD]
[TD="align: right"]1/1/2022[/TD]
[TD="align: right"]1/1/2023[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]12/31/2019[/TD]
[TD="align: right"]12/31/2020[/TD]
[TD="align: right"]12/31/2021[/TD]
[TD="align: right"]12/31/2022[/TD]
[TD="align: right"]12/31/2023[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]736[/TD]
[TD="align: right"]737[/TD]
[TD="align: right"]738[/TD]
[TD="align: right"]739[/TD]
[TD="align: right"]740[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2/1/1960[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8/14/1958[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8/9/1964[/TD]

</tbody>

Sheet432​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C4[/TH]
[TD="align: left"]=IF(AND(YEAR(EDATE($B4,C$3))=YEAR(C$2),COUNTIF($B4:B4,"Yes")=0),"Yes","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Formula copied down and across.

 

[jtakw]

You're welcome, welcome to the forum.

Realized my formula can be slightly shorter, modified below:

Book1
	A	B	C	D	E	F	G
1		From	1/1/2019	1/1/2020	1/1/2021	1/1/2022	1/1/2023
2		To	12/31/2019	12/31/2020	12/31/2021	12/31/2022	12/31/2023
3		Required age (months)	736	737	738	739	740
4	DOB	2/1/1960			Yes
5		8/14/1958	Yes
6		8/9/1964
Sheet432

Cell Formulas
Range	Formula
C4	=IF(AND(EDATE($B4,C$3)<=C$2,COUNTIF($B4:B4,"Yes")=0),"Yes","")