Automatic change of cell contents according to date

Please revise your profile to show your current version of Excel. There is no Excel 2020.

Let me restate your description to make sure it's clear, because you give an example but don't explain how to determine E6 or F6.

There is a list of dates in column K. The date in column E should be the next date from column K that follows the date in D6 (i.e., TODAY()). So that if today is any day in 2025, the date in E6 should be 31 DEC 2025. The value in F6 should be the value in column L that correspond to the date in E6.

See attached.

That is a completely new question.

Here is the solution, but column Q is blank so I'm not sure why you want to include it.

Values as displayed

	D	E	F
5	TODAY
6	3/16/2024	12/31/2024	3000
7			5400
8			700
Solution

Underlying formulas

	D	E	F
5
6	=TODAY()	=INDEX($K$3:$K$9,MATCH(D6,$K$3:$K$9,1)+1)	=SUM(INDEX($L$3:$L$7,MATCH($D$6,$K$3:$K$7,1)+1):INDEX($N$3:$N$7,MATCH($D$6,$K$3:$K$7,1)+1))
7			=SUM(INDEX($L$3:$L$7,MATCH($D$6,$K$3:$K$7,1)+1),INDEX($N$3:$N$7,MATCH($D$6,$K$3:$K$7,1)+1),INDEX($O$3:$O$7,MATCH($D$6,$K$3:$K$7,1)+1))
8			=SUM(INDEX($M$3:$M$7,MATCH($D$6,$K$3:$K$7,1)+1),INDEX($Q$3:$Q$7,MATCH($D$6,$K$3:$K$7,1)+1))

Completely new question. Normally I would ask you to start a new thread but since I have a solution we'll take care of it here.

Values as displayed

	F	G
9	TODAY
10	3/16/2024	7/20/2023
Hárok1

Underlying formulas

	F	G
9
10	=TODAY()	=DATE(YEAR(F10)-IF(F10>=DATE(YEAR(F10),7,20),0,1),7,20)

You're welcome and thanks for the rep!

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."