# Formula Taking A Date & Returning A Year

#### SteveP29

##### New Member
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

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### BobUmlas

##### Well-known Member
=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

##### Board Regular
No lookup is necessary. This will return the fiscal year based on April start of FY. In E2:
Excel Formula:
``=IF(MONTH(D2)<4,YEAR(D2),YEAR(D2)+1)``

#### JamesCanale

##### Board Regular
No lookup is necessary. This will return the fiscal year based on April start of FY. In E2:
Excel Formula:
``=IF(MONTH(D2)<4,YEAR(D2),YEAR(D2)+1)``
I think I got your years wrong...
Excel Formula:
``if(month(D2)<4,year(D2)-1,Year(D2))``

#### SteveP29

##### New Member

I think I got your years wrong...
Excel Formula:
``if(month(D2)<4,year(D2)-1,Year(D2))``
Trying both of these formulas still returns 1905

#### SteveP29

##### New Member
=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

#### Attachments

• Validation Sheet.jpg
106.3 KB · Views: 6

#### JamesCanale

##### Board Regular

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

##### New Member
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

#### Attachments

• Validation Sheet 2.jpg
123.6 KB · Views: 6

#### Fluff

##### MrExcel MVP, Moderator
With this formula
Excel 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

##### New Member
With this formula
Excel 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)

Replies
6
Views
209
Replies
15
Views
196
Replies
8
Views
180
Replies
14
Views
214
Replies
5
Views
110

1,127,808
Messages
5,627,010
Members
416,214
Latest member
boston814

### 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.

### Which adblocker are you using?

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

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