+ Reply to Thread
Page 2 of 2 FirstFirst 1 2
Results 16 to 25 of 25

How to spread recurring bill over the years having lease start and end date

  1. #16
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: How to spread recurring bill over the years having lease start and end date

    No, my formula would not account for that. I can take another look at it to see how that might be accomplished.
    If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.

    If your question has been answered, mark the thread as [SOLVED] using the Thread Tools menu at the top of the thread.

  2. #17
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: How to spread recurring bill over the years having lease start and end date

    Updated formula to account for lease starting and ending in same year:
    Please Login or Register  to view this content.
    This formula is rather complicated for my tastes, despite accomplishing the purpose. I'm going to try and trim it down, but it should work for what you need.

  3. #18
    Valued Forum Contributor Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    1,125

    Re: How to spread recurring bill over the years having lease start and end date

    Have a try with this formula I used the DATEDIF function you also use for no of terms too.


    Formula: copy to clipboard
    Please Login or Register  to view this content.


    this formula also works if start and end year are the same.

    see attachment of working formula

    ___________________
    @Melvosh: you cannot post your formula when you have greater than or less then sign followed by number or text, this is a firewall protection to avoid script injection
    my formula works because it has the = behind the <= you can resolve by putting a space behind the "< "
    also look at this post about Sucuri issues https://www.excelforum.com/tips-and-...rkarounds.html
    Attached Files Attached Files
    Last edited by Roel Jongman; 05-16-2019 at 05:32 PM.

  4. #19
    Registered User
    Join Date
    05-16-2019
    Location
    Canada
    MS-Off Ver
    365
    Posts
    14

    Re: How to spread recurring bill over the years having lease start and end date

    Hey Melvosh Thanks alot.
    I haven't had a chance to try this yet as I am still in the process of building the whole model. I will apply this once I am in that part again and will let you know
    Thanks once again it was such a great help
    Mohammad

  5. #20
    Registered User
    Join Date
    05-16-2019
    Location
    Canada
    MS-Off Ver
    365
    Posts
    14

    Re: How to spread recurring bill over the years having lease start and end date

    Thanks alot Roel for your wonderful observation and a fantastic solution in the worksheet :D it really worked well in the given scenario
    Thank you for your help, much appreciated

    Mohammad

  6. #21
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: How to spread recurring bill over the years having lease start and end date

    Happy to help. FYI, I would personally go with Roel's formula in post # 18, it's much more compact.

  7. #22
    Registered User
    Join Date
    05-16-2019
    Location
    Canada
    MS-Off Ver
    365
    Posts
    14

    Re: How to spread recurring bill over the years having lease start and end date

    Thanks Melvosh. I would apply both the formulas in my worksheet to see which works the best .. I agree that Roel's formula is much more compact ..
    Thanks again guys ....
    Let me get back to you with the good news once it has been built up ..

  8. #23
    Registered User
    Join Date
    05-16-2019
    Location
    Canada
    MS-Off Ver
    365
    Posts
    14

    Re: How to spread recurring bill over the years having lease start and end date

    Hi Melvosh and Roel.
    I applied Roel's formula and it work just superbly on the sheet.
    I want to thank you people from the bottom of my heart for coming up with such an amazing solution to this .
    Thanks a ton.

  9. #24
    Valued Forum Contributor Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    1,125

    Re: How to spread recurring bill over the years having lease start and end date

    your welcome, glad to been able to help..

  10. #25
    Registered User
    Join Date
    05-16-2019
    Location
    Canada
    MS-Off Ver
    365
    Posts
    14

    Re: How to spread recurring bill over the years having lease start and end date

    it was a great help.
    Thanks once again guys ..

+ Reply to Thread
Page 2 of 2 FirstFirst 1 2

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1