I have a spreadsheet that for each item calculates the number of days that fall within the financial year and then the cost that would relate to it.
The formula is:
=I10*((MAX(MIN($Q$1,H10)-MAX($P$1,G10)+1,0))/365)
Where:-
P1: first date in the current financial year being reported - 01/04/2019
Q1: last date in the current financial year being reported - 31/03/2020
Column G: Commencement date of contract
Column H: Termination date of contract
Column I: Annual Rent
The annoying thing is that this worked for 18/19 and it works for 20/21 but I just can't figure out why it won't work for 19/20. It seems to be counting an extra day but I don't understand why or what to do. Any help would really be appreciated. Thanks.
The formula is:
=I10*((MAX(MIN($Q$1,H10)-MAX($P$1,G10)+1,0))/365)
Where:-
P1: first date in the current financial year being reported - 01/04/2019
Q1: last date in the current financial year being reported - 31/03/2020
Column G: Commencement date of contract
Column H: Termination date of contract
Column I: Annual Rent
The annoying thing is that this worked for 18/19 and it works for 20/21 but I just can't figure out why it won't work for 19/20. It seems to be counting an extra day but I don't understand why or what to do. Any help would really be appreciated. Thanks.
