Use of DATE(YEAR($F7)+$G7,12,31) in an IF statement

[SPRoyLLC]

I'm trying to use the function =DATE(YEAR($F7)+$G7,12,31) in an IF statement but the results are mixed.

Please refer to the attached excel file to review a sample worksheet layout and data content.

Beginning in Cell O6 I entered this formula =IF(YEAR(O$1)>DATE(YEAR($F6)+$G6,12,31),0,1) and copied it through Cell Y6. For Cells O6 thru S6 (years 2014 thru 2018) I expected the formula to report 1 (one) and in years 2019 through 2024 I expected a 0 (zero). Instead I received a 1 in all years. What did I do wrong?

Beginning in Cell O7, I incorporated the above formula with a VLookup function. This formula again tests if the year in, for example, cell O$1 (i.e., 12/31/2014) is greater than the "end year" that is calculate using cells $F7 and G7 (Date(Year(F7)+G7,12,31) and - if true report 0, else perform the VLookup function. I copied the formula in Cell O7 through to Cell Y7 and on the attached Excel file you will see some mixed results.

Beginning in Cell O8 thru Y8, I've entered the correct values, which are manually calculated.

Thank you for any help you can provide in helping me make sense of what the formula in row 6 and 7 should be.

[FDibbins]

I will take a look at your file, but I can already see where I think the problem is...
YEAR(O$1) till give you a 4-digit number (2012 for instance)
DATE(YEAR($F6)+$G6,12,31) will give you a 5-digit number (41558 for today, for instance)
4 digits will never ne greater than 5 digits, so you will always get 1

edit: Yup, I was right, try this instead...
=IF(YEAR(O$1)>YEAR($F6)+$G6,0,1)

[daffodil11]

The first issue I see is the IF(YEAR(O$1)>DATE(YEAR($F6)+$G6,12,31),0,1)

You're pulling just a year out a date and then comparing it to a whole date. The year will never be larger than the value of a given date.

Either omit the first year reference, or just compare straight values.

=IF(O$1>DATE(YEAR($F6)+$G6,12,31),0,1)

or

=IF(YEAR(O$1)>YEAR($F6)+$G6,0,1)



Edit: Yeah, what he said.

[SPRoyLLC]

Thanks a million - your fix works wonderfully in all row 6 cells. Years O1 thru S1 report a "1" while the remaining rows now report a zero. That is very good.

However, when I tried adding the formula to the row 7 formula with the VLOOKUP function it seems to work in all cells except cell S7 (year 2018), which still results in zero. I tried using >= in the formula but it still reported a zero. Also, the values in cells Q7 thru S7 do not match the manually calculated values in row 8. Any ideas?

[FDibbins]

You are getting a 0 hrere because that is what the vlookup is returning

[SPRoyLLC]

Thank you. Now that you've pointed me in the right direction, I think I know what's wrong. Sure wish I had your Excel knowledge.

[FDibbins]

Happy tp help