Value based on the date

[kesha_11]

Hello, I would like to ask for some advice in regard to an issue I am having with a formula. I'm trying to create a spreadsheet with the contract start date, end date, total value, charges to be paid in the first year depending on the total value and the charges to be paid in the rest of the contract period depending on the total value. So I have these 5 columns including the charges to be paid in the first year depending on the total value and the charges to be paid in the rest of the contract period depending on the total value which are both calculated separately. I now need to break down these costs according to the financial years. So, if the contract started in 2019, I need the first year value to fall in the 2019-2020 column and if the contract didn't start in 2019, I need the value of the rest of the contract to fall into the 2019 column and so on. Please see the attached screenshot of the table as a reference. And if the contract ended in 2025, I would like the columns with 2026-2027 to say "0", as the contract has ended and there are no more charges to be paid. I am not sure if this is achievable but I have tried several formulas but it didn't work as I would like it to. Thanks a lot in advance!! 

[#2 · Accepted solution]

@kesha_11 

Change Table1 to the name of the table.

=IF(--LEFT(Table1[[#Headers],[2019-2020]],4)=YEAR(Table1[@[Contract Start Date]:[Contract Start Date]]),Table1[@[Fees to be paid in the first year]:[Fees to be paid in the first year]],IF(AND(--LEFT(Table1[[#Headers],[2019-2020]],4)>YEAR(Table1[@[Contract Start Date]:[Contract Start Date]]),--LEFT(Table1[[#Headers],[2019-2020]],4)<=YEAR(Table1[@[Contract End Date]:[Contract End Date]])),Table1[@[Fees to be paid for the rest of the contract]:[Fees to be paid for the rest of the contract]],""))

[#3]

Hello,

Thank you very much for this and I apologise for the delay in getting back to you.

I tried this formula but I keep getting a #VALUE error. Not sure why..

[#4]

@kesha_11 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#5]

Hi, it was actually my bad, I tried the formula again and it works perfectly!! Thank you so much for this!