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

joy1mat

New Member
Joined
Jun 26, 2016
Messages
31
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKL
1SURNAMECHRISTIAN NAMETypeDATESTATECOUNTRYRefYear difType value
2D7/02/1957QLD AUS537265B100
3D29/04/1967QLD AUS537355M75
4B19/04/1949QLD AUS537473D40
5B16/09/1973QLD AUS537548
6M19/01/1861ABDSCT5376#VALUE!
7M19/01/1861ABDSCT5376#VALUE!
8D8/1/1861ABDSCT5376#VALUE!
9D21/05/1980ABDSCT537642
Sheet1
Cell Formulas
RangeFormula
H2:H9H2=DATEDIF(D2,TODAY(),"y")


Joyce
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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")
 
Upvote 0
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
 
Upvote 0
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. :)

1658892972903.png


Would something like one of these be any use?

22 07 27.xlsm
CDEFGHI
1TypeDATESTATECOUNTRYRefAbove/Below/EqualAbove/Below/Equal
2D7/02/1957QLD AUS5372AboveAbove
3D29/04/1967QLD AUS5373AboveAbove
4B19/04/1949QLD AUS5374BelowBelow
5B16/09/1973QLD AUS5375BelowBelow
6M19/01/1861ABDSCT5376#VALUE!Above
7M19/01/1861ABDSCT5376#VALUE!Above
8D8/1/1861ABDSCT5376#VALUE!Above
9D21/05/1980ABDSCT5376AboveAbove
10M27/07/1947EqualEqual
Above Below
Cell Formulas
RangeFormula
H2:H10H2=CHOOSE(SIGN(DATEDIF(D2,TODAY(),"y")-VLOOKUP(C2,K$2:L$4,2,0))+2,"Below","Equal","Above")
I2:I10I2=IFERROR(CHOOSE(SIGN(DATEDIF(D2,TODAY(),"y")-VLOOKUP(C2,K$2:L$4,2,0))+2,"Below","Equal","Above"),"Above")
 
Upvote 0
Solution
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
CDEFGHI
1TypeDATESTATECOUNTRYRefAbove/Below/EqualAbove/Below/Equal
2D7/02/1957QLD AUS5372AboveAbove
3D29/04/1967QLD AUS5373AboveAbove
4B19/04/1949QLD AUS5374BelowBelow
5B16/09/1973QLD AUS5375BelowBelow
6M19/01/1861ABDSCT5376#VALUE!Above
7M19/01/1861ABDSCT5376#VALUE!Above
8D8/1/1861ABDSCT5376#VALUE!Above
9D21/05/1980ABDSCT5376AboveAbove
10M27/07/1947EqualEqual
Above Below
Cell Formulas
RangeFormula
H2:H10H2=CHOOSE(SIGN(DATEDIF(D2,TODAY(),"y")-VLOOKUP(C2,K$2:L$4,2,0))+2,"Below","Equal","Above")
I2:I10I2=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
 
Upvote 0

Forum statistics

Threads
1,223,970
Messages
6,175,721
Members
452,667
Latest member
vanessavalentino83

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top