Compile formula to identify if a value is greater or less than the exclusion value based on type

[joy1mat]

There's 2 formulas I'm trying to figure out. The first one I think I got right which is in column H (Year Dif). The next one I'm having trouble with. I need to either use a conditional formatting formula that will look up a type value and highlight a cell based on whether this is greater or less than a date difference value. So for Row 2 is the value in H2 greater or less than L4 which is the value for the type in C2. In this case the value is greater though for row 4 H4 is less than the value of 100 (L2) for the type in C4.

Whether I need just 1 formula with conditional formatting or just 1 formula that does both the Year Dif and returns a true/false value I don't know. I hope my spreadsheet clearly shows what I'm trying to achieve. Any help would be appreciated.

Action item 17.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	SURNAME	CHRISTIAN NAME	Type	DATE	STATE	COUNTRY	Ref	Year dif			Type value
2			D	7/02/1957	QLD	AUS	5372	65			B	100
3			D	29/04/1967	QLD	AUS	5373	55			M	75
4			B	19/04/1949	QLD	AUS	5374	73			D	40
5			B	16/09/1973	QLD	AUS	5375	48
6			M	19/01/1861	ABD	SCT	5376	#VALUE!
7			M	19/01/1861	ABD	SCT	5376	#VALUE!
8			D	8/1/1861	ABD	SCT	5376	#VALUE!
9			D	21/05/1980	ABD	SCT	5376	42
Sheet1

Cell Formulas
Range	Formula
H2:H9	H2	=DATEDIF(D2,TODAY(),"y")

Joyce

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It is not clear to me what end-result you are after.
- Are you trying to colour cells based on the year diff compared to the relevant value from the table in columns K:L? If so, which cells do you actually want coloured and are you looking for, say blue if above and yellow if below? If you are looking for colour, are you also trying to eliminate the 'Year dif' column? Please clarify

- Are looking for a cell formula (not conditional formatting) that simply says "Above" or "Below" (or perhaps "Equal")

 

[joy1mat]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It is not clear to me what end-result you are after.
- Are you trying to colour cells based on the year diff compared to the relevant value from the table in columns K:L? If so, which cells do you actually want coloured and are you looking for, say blue if above and yellow if below? If you are looking for colour, are you also trying to eliminate the 'Year dif' column? Please clarify

- Are looking for a cell formula (not conditional formatting) that simply says "Above" or "Below" (or perhaps "Equal")

Hello Peter

I'm using Office 365 and Windows 10 if that helps.

Sorry it's not clear what end results I'm after. Bit hard to explain. Basically I have a genealogical index of births, deaths and marriages. Under Type (Column C) the B is for Birth, D is Death and M Marriage. I need to be able to highlight in some way for each record (row) whether the date difference is greater or less than the exclusion timeframe. Row 2 is a death so we need to protect the privacy of someone who has been dead less than 40 years. The type values for these are in Column K & L eg a birth it's 100 years and a marriage it's 75 years.

I'm looking for the simplest way to identify this information regardless of whether it says "Above" or "Below" or even "Equal". The cell doesn't have to be coloured. I thought I could do this as a conditional formatting but couldn't work out how to do it. I actually like the idea you have with a formula to say Above, Below or Equal. The workbook will then become a on-going point of reference to check for any records that were Below in 2022 but Above in 2023 for example. I used today in the date difference formula so I'm hoping that is correct.

I'd like to keep the Year dif column and formula. As some of the dates are pre 1900 I know the date formula won't work on these though the fact the formula returns a #value is fine because I know all these records are above all the date values and are ok.

Apologies for the novel - hope this is clearer what I'm after.

Joyce

 

[Peter_SSs]

I'm using Office 365 and Windows 10 if that helps.

Please add that to your account details as described above so that the information is always available to helpers like mine below.

Would something like one of these be any use?

22 07 27.xlsm
	C	D	E	F	G	H	I
1	Type	DATE	STATE	COUNTRY	Ref	Above/Below/Equal	Above/Below/Equal
2	D	7/02/1957	QLD	AUS	5372	Above	Above
3	D	29/04/1967	QLD	AUS	5373	Above	Above
4	B	19/04/1949	QLD	AUS	5374	Below	Below
5	B	16/09/1973	QLD	AUS	5375	Below	Below
6	M	19/01/1861	ABD	SCT	5376	#VALUE!	Above
7	M	19/01/1861	ABD	SCT	5376	#VALUE!	Above
8	D	8/1/1861	ABD	SCT	5376	#VALUE!	Above
9	D	21/05/1980	ABD	SCT	5376	Above	Above
10	M	27/07/1947				Equal	Equal
Above Below

Cell Formulas
Range	Formula
H2:H10	H2	=CHOOSE(SIGN(DATEDIF(D2,TODAY(),"y")-VLOOKUP(C2,K$2:L$4,2,0))+2,"Below","Equal","Above")
I2:I10	I2	=IFERROR(CHOOSE(SIGN(DATEDIF(D2,TODAY(),"y")-VLOOKUP(C2,K$2:L$4,2,0))+2,"Below","Equal","Above"),"Above")

 

[joy1mat]

Please add that to your account details as described above so that the information is always available to helpers like mine below.

View attachment 70197

Would something like one of these be any use?

22 07 27.xlsm
	C	D	E	F	G	H	I
1	Type	DATE	STATE	COUNTRY	Ref	Above/Below/Equal	Above/Below/Equal
2	D	7/02/1957	QLD	AUS	5372	Above	Above
3	D	29/04/1967	QLD	AUS	5373	Above	Above
4	B	19/04/1949	QLD	AUS	5374	Below	Below
5	B	16/09/1973	QLD	AUS	5375	Below	Below
6	M	19/01/1861	ABD	SCT	5376	#VALUE!	Above
7	M	19/01/1861	ABD	SCT	5376	#VALUE!	Above
8	D	8/1/1861	ABD	SCT	5376	#VALUE!	Above
9	D	21/05/1980	ABD	SCT	5376	Above	Above
10	M	27/07/1947				Equal	Equal
Above Below

Cell Formulas
Range	Formula
H2:H10	H2	=CHOOSE(SIGN(DATEDIF(D2,TODAY(),"y")-VLOOKUP(C2,K$2:L$4,2,0))+2,"Below","Equal","Above")
I2:I10	I2	=IFERROR(CHOOSE(SIGN(DATEDIF(D2,TODAY(),"y")-VLOOKUP(C2,K$2:L$4,2,0))+2,"Below","Equal","Above"),"Above")

Hi Peter

I've gone with the 2nd formula. This is really great and works perfect. Thank you so much. Joyce

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.