The cell value is filled according to YEAR from dates. How to do it?

[seanxx]

Hello friends,

In cell "E11" I need to add values from column "J"
when a specific case occurs in formula "E11", but
the formulas in cells "B5, E8, G8" do not change.

when the year is 2024 in cell E8, then E11=100
when the year is 2025 in cell G8, then E11=200
when the year is 2026 in cell G8, then E11=300
when the year is 2027 in cell E8, then E11=400

Could you please help me with this?

Thank you

Zošit1
	D	E	F	G	H	I	J	K
2
3
4
5		1.1.2025					100
6							200
7							300
8		28.2.2024		1.1.2025			400
9
10
11		100
12
13
14
Hárok1

Cell Formulas
Range	Formula
E5	E5	=TODAY()
E8	E8	=DATE(YEAR($E$5)-IF($E$5>=DATE(YEAR($E$5),2,28),0,1),2,28)
G8	G8	=DATE(YEAR($E$5)-IF($E$5>=DATE(YEAR($E$5),1,1),0,1),1,1)
E11	E11	=IF(YEAR(E8)=2024,J5,IF(YEAR(G8)=2025,J6,IF(YEAR(G8)=2026,J7,IF(YEAR(E8)=2027,J8,))))

 

[Joe4]

There is a problem with your logic - or at least it has not been completely flushed out/explained.

Here are the conditions you laid out:

when the year is 2024 in cell E8, then E11=100
when the year is 2025 in cell G8, then E11=200
when the year is 2026 in cell G8, then E11=300
when the year is 2027 in cell E8, then E11=400

In the example you posted, the year in E8 is 2024, AND the year in G8 is 2025.
So BOTH your top two conditions are met.
So which ones wins?
Should E11 show 100 (according to your first rule) or should it show 200 (according to your second rule)?

Also, is J5-J8 ALWAYS going to be for years 2024-2027?
If not, and they may change, you should show a 2 column table, showing which values are associated with which years.
This will allow you to update the sheet for subsequent years just by changing that data table, and not having to change your formulas.

Lastly, it appears to me that the dates entered in E5, E8, and G8 are actually entered as Text, and not as valid Dates.
I say this because of the odd format, and the little triangle in the upper left corner of those cells, which usually indicates numeric values entered as text.
Can you confirm this is the case?

If you are not sure, enter this formula in any blank cell and tell us what it returns:

=ISNUMBER(E8)

 

[seanxx]

I will add:
Now show me E11=200

formula =ISNUMBER(E8) returns "TRUE"

 

[Joe4]

Please answer ALL the questions I asked in my previous posts, especially the one about which rule wins out if two conditions are met.
This is essential in creating the formula! If we don't know what value you expect when multiple conditions are met at the same time, we don't know what to create.

 

[seanxx]

"So which ones wins?
Should E11 show 100 (according to your first rule) or should it show 200 (according to your second rule)?"
Cell E11 is the only cell to display the result.
For the result in cell E11, ONLY the SECOND rule of the formula in this cell is valid NOW, so,
if cell G8 is the year 2025, then E11=J6 (200).
Last year, the first rule of the formula was valid for the cell...

"Also, is J5-J8 ALWAYS going to be for years 2024-2027?"
Yes, J5-J8 are ALWAYS for 2024-2027

"Lastly, it appears to me that the dates entered in E5, E8, and G8 are actually entered as Text, and not as valid Dates.
I say this because of the odd format, and the little triangle in the upper left corner of those cells, which usually indicates numeric values entered as text.
Can you confirm this is the case?"
The dates are entered as DATES, not as text, I don't have the little yellow triangles in the upper left corner.
Return "TRUE" to the free cell after inserting =ISNUMBER(E8)

 

[Joe4]

OK, try this formula in cell G11:

=INDEX(J5:J8,YEAR(IF(G8<>"",G8,E8))-2023)

 

[seanxx]

@Joe4
yes, this works.
Thank you very much for your patience and help.

 

[Joe4]

You are welcome.
Glad I was able to help!