Formula Taking A Date & Returning A Year

SteveP29

New Member
Joined
Jul 1, 2007
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

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
Joined
Mar 14, 2002
Messages
1,181
=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
Joined
Jan 13, 2021
Messages
126
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
Joined
Jan 13, 2021
Messages
126
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
Joined
Jul 1, 2007
Messages
20
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jul 1, 2007
Messages
20
Office Version
  1. 365
Platform
  1. Windows
=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
    Validation Sheet.jpg
    106.3 KB · Views: 6

JamesCanale

Board Regular
Joined
Jan 13, 2021
Messages
126
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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
Joined
Jul 1, 2007
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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
    Validation Sheet 2.jpg
    123.6 KB · Views: 6

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,654
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 1, 2007
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top