Results 1 to 7 of 7
Thread: Month Calculation

20010301, 20:22 #1CChambersGuest
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

20010301, 20:32 #2
 Join Date
 Feb 2001
 Location
 Dublin, Ireland, Republic of
 Posts
 2,697
 Thanks
 1
 Thanked 0 Times in 0 Posts
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

20010301, 20:55 #3CChambersGuest
Re: Month Calculation
Thanks. Let me know if it doesn't come through.

20010301, 21:00 #4CChambersGuest
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.

20010301, 21:38 #5CChambersGuest
Re: Month Calculation
Ok. I've clarified exactly what I need. On the attachment, in YR1, it shows JAN01 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 JAN01 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

20010301, 22:06 #6
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 JAN01. You can then format the cells to get it displayed as Jan01.Legare Coleman

20010301, 22:08 #7
 Join Date
 Feb 2001
 Location
 Dublin, Ireland, Republic of
 Posts
 2,697
 Thanks
 1
 Thanked 0 Times in 0 Posts
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.