1. ## Month Calculation

I have an input sheet which a user puts in a starting year. On 10 consective spreadsheets, I have the monthly columns shown for each consecutive year. I have a tenant whose rent starts at a particular month/year. I want my formula to read what month/year in in the spreadsheet and record the rent if accurate. I am having a problem with the monthly calculations.

Example:
10 years, starting at 2001 through 2001
Tenant A moves in during June of 2002

I may not be clear in what I'm asking for. If anyone needs to see a sample and can help, please let me know.

thanks
christine

2. ## Re: Month Calculation

Christine,

As it's not clear exactly what your problem is (at least to me) maybe you could upload a sample and we can take it from there.

regards,

Andrew C

3. ## Re: Month Calculation

Thanks. Let me know if it doesn't come through.

4. ## Re: Month Calculation

So, in reference to my specific sample, I want the formula to look at the fact that it is Jan 2001. Because Sungard's lease began in 1998, I want it to calculate the monthly rent and record it in the Jan 2001 column.

5. ## Re: Month Calculation

Ok. I've clarified exactly what I need. On the attachment, in YR1, it shows JAN-01 which is pulled from an input sheet. I want the formula in the next column to calc the next month, and so on.

Then, I've written a formula in JAN-01 column which says if the lease start date is greater than, equal to the month of the column...Then calculate the rent for the month.

The part I am not getting is the monthly calculation for each column header.

thanks
christine

6. ## Re: Month Calculation

If your starting date is in A1, then the following formla will calculate the first of the next month:

<pre>=DATE(YEAR(A1),MONTH(A1)+1,1)
</pre>

That formula can be copied across as many columns as you need. The date in A1 must be entered as a date, not a text string JAN-01. You can then format the cells to get it displayed as Jan-01.

7. ## Re: Month Calculation

Hi Christine,

I have inserted a formula in cell E10 of sheet YR 1 which calculates the rate due if the lease commenced before the month in question.

I did the change the month headings to contain the actual date rather JAN for January. I set the date to the first of the month, but you can change that. It is formatted to show just JAN. I did this so that I could have a date to compare with lease start date. If the lease had not started before Jan then 0 is entered.

I hope you can follow and apply it to the rest of your spreadsheet, (always assuming I have got it right !!! - hope I did understand the problem)

Good Luck

Andrew
P.S. I did this before I spotted you posted a second sheet.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•