Hello,
Which formula could help allocate contract amounts by year using the start and end date of the specific contract. Example below:
Headers:
Column C = Start Date
Column D = End Date
Column E = Amount
Column F = Number of Days (Column D - Column C)
Column G = Amount Per Day (Column E / Column F)
Column H = 2015 Year End
Column I = 2016 Year End
Column J = 2017 Year End
.... (repeating pattern until):
Column R = 2025 Year End
Contracts:
Cell C2 = April 18, 2015
Cell D2 = April 17, 2025
Cell E2 = $5,000
Based on the information above, I would expect the formulas populated in H2 through to R2 to calculate the amount of the contract that was applicable to that year. 2015 and 2025 would be prorated (based on the number of days in those years) and the full years (2016-2024) would roughly equal amounts based on the number of days in the year (including leap years).
Can anyone help with this?
Thanks in advance!
Which formula could help allocate contract amounts by year using the start and end date of the specific contract. Example below:
Headers:
Column C = Start Date
Column D = End Date
Column E = Amount
Column F = Number of Days (Column D - Column C)
Column G = Amount Per Day (Column E / Column F)
Column H = 2015 Year End
Column I = 2016 Year End
Column J = 2017 Year End
.... (repeating pattern until):
Column R = 2025 Year End
Contracts:
Cell C2 = April 18, 2015
Cell D2 = April 17, 2025
Cell E2 = $5,000
Based on the information above, I would expect the formulas populated in H2 through to R2 to calculate the amount of the contract that was applicable to that year. 2015 and 2025 would be prorated (based on the number of days in those years) and the full years (2016-2024) would roughly equal amounts based on the number of days in the year (including leap years).
Can anyone help with this?
Thanks in advance!