Results 1 to 7 of 7
  1. #1
    CChambers
    Guest

    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. #2
    Gold Lounger
    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

  3. #3
    CChambers
    Guest

    Re: Month Calculation

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

  4. #4
    CChambers
    Guest

    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. #5
    CChambers
    Guest

    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. #6
    Uranium Lounger
    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 JAN-01. You can then format the cells to get it displayed as Jan-01.
    Legare Coleman

  7. #7
    Gold Lounger
    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.
    Attached Files Attached Files

Posting Permissions

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