Advanced Logic Question - Is It Possible

[billybobb]

Hi,

I have a spreadsheet with a set of dates and rent charged on certain dates. Rent changes on certain dates, I would like to automate the spreadsheet so I do not have to manually go in and spread out the rent based on the dates. Essentially this is a rent schedule and I want the total rent to populate automatically in each calender year without the need for manual calculations.

Thanks for your help.

[incjourn]

Try something like this simply with spreadsheet formulae
In new sheet
1. In column A, set up a list of all the first days of the month from 2009 to 2019 (1st Jan 09, 1st Feb 09 ... etc)
2. Populate column B with year using =year(date) function
2. Populate column C with rates
3. In a seperate summary table for each year use =sumif(A1:A300,">"&?End-Date?,C1:C300)-sumif(A1:A300,"<="&?Start-Date?,C1:C300)