Forum Discussion
kesha_11
Apr 26, 2023Copper Contributor
Value based on the date
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...
- Apr 26, 2023
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]],""))
HansVogelaar
Apr 26, 2023MVP
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]],""))
- kesha_11May 02, 2023Copper ContributorHello,
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..- HansVogelaarMay 02, 2023MVP
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?
- kesha_11May 02, 2023Copper ContributorHi, it was actually my bad, I tried the formula again and it works perfectly!! Thank you so much for this!