Formula Taking A Date & Returning A Year

[SteveP29]

Hi,
I'm working with a table, column D is populated with a date (cell D2 is 15/04/2020)
Column E is entitled Year.
I want the data in column E to equal the financial year (ie dates between 01/04/2020 and 31/03/2021 should return the result 2020 and so on)
On a separate worksheet (named Validation) I have the parameters for the calculation
A1 has Date From
B1 has Date To
C1 has Year

A2- A11 has the first of April for each year from 2020 to 2029
B2- B11 has the 31st March for each year from 2021 to 2030
C2- C11 has 2020 to 2029

I have tried the following formula: =LOOKUP(2,1/($A$2:$A$11<=[@[Date of Application]])/$B$2:$B11>=[@[Date of Application]]),$C$2:$C$11)
NB: [@[Date of Application]] is what appears in the formula when I reference D2 (15/04/2020 above)

The formula should return 2020 for cell E2 but it returns 1905

I don't understand why it isn't working, is it because the date I'm trying to use to define the year is within a table?
Or is it because my parameters are on a different sheet? because when I enter the formula in the Validation worksheet where the date parameters are, it works perfectly

Apologies for not posting screenshots or the file, but as it is for work and contains GDPR sensitive and personal data

Thanks in advance for any help

 

[BobUmlas]

=YEAR(2020) is 1905. 2020 is not a date, it's a year. the 2,020th day since 1/1/1900 is in 1905.
You want to reference the cell itself, not the YEAR of that cell!

 

[JamesCanale]

No lookup is necessary. This will return the fiscal year based on April start of FY. In E2:

=IF(MONTH(D2)<4,YEAR(D2),YEAR(D2)+1)

 

[JamesCanale]

No lookup is necessary. This will return the fiscal year based on April start of FY. In E2:

=IF(MONTH(D2)<4,YEAR(D2),YEAR(D2)+1)

I think I got your years wrong...

if(month(D2)<4,year(D2)-1,Year(D2))

 

[SteveP29]

I think I got your years wrong...

if(month(D2)<4,year(D2)-1,Year(D2))

Trying both of these formulas still returns 1905

 

[SteveP29]

=YEAR(2020) is 1905. 2020 is not a date, it's a year. the 2,020th day since 1/1/1900 is in 1905.
You want to reference the cell itself, not the YEAR of that cell!

Apologies, I'm not following what you mean, what do I need to change to do what you're suggesting?

I think its confusing me more because the examples I've run below the validation work perfectly (screenshot uploaded to show, realised there were no privacy breaches
NB I have changed the end date to the 1st April for each year as 31st March produced an N/A error

 

[JamesCanale]

Trying both of these formulas still returns 1905

D2 should contain a date, not just a four digit number. In your original post, D2 contains April 15, 2020.

Excel stores dates as the number of days since January 1, 1900. If you format the current date as 'general', you'll get a 44,211. - that works out to 120 years worth of days. 2000 days is about 5 years worth of days, so 1905.

Use the formula above on a 'date', not a year.

 

[SteveP29]

D2 should contain a date, not just a four digit number. In your original post, D2 contains April 15, 2020.

Excel stores dates as the number of days since January 1, 1900. If you format the current date as 'general', you'll get a 44,211. - that works out to 120 years worth of days. 2000 days is about 5 years worth of days, so 1905.

Use the formula above on a 'date', not a year.

The date column (D) is formatted as a date

 

[Fluff]

With this formula

=LOOKUP(2,1/($A$2:$A$11<=[@[Date of Application]])/$B$2:$B11>=[@[Date of Application]]),$C$2:$C$11)

What sheet is the formula on & what sheet are A2:C11 on?

 

[SteveP29]

With this formula

=LOOKUP(2,1/($A$2:$A$11<=[@[Date of Application]])/$B$2:$B11>=[@[Date of Application]]),$C$2:$C$11)

What sheet is the formula on & what sheet are A2:C11 on?

The data (in a table) and formula (also within the table) is on a worksheet called Data (picture 2 posts above showing the formatting)
A2:C11 are on a separate sheet called Validation (attachment 4 posts above)