+ Reply to Thread
Results 1 to 5 of 5

monthly split of contract value

  1. #1
    Registered User
    Join Date
    02-17-2008
    Posts
    70

    monthly split of contract value

    Hi All,

    I was able to come up with below formula for splitting contract value into months for revenue based on start and end date of the contract.
    Please Login or Register  to view this content.
    D7 = start Date
    E7 = end date
    G6 = beg of current month
    H7 = end of current month (ie beg of next month)
    F7 = annaul contract value.

    This formula works perfectly for 365 days calculation, i want to make it into 360 days calculation, so that revenue amount is same for all the months.

    I have attached the file.
    would really appreciate your help

    Thks

    Nazim
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,400

    Re: monthly split of contract value

    Hi,

    It's not clear, at least to me, exactly what you're asking for. If you want the revenue the same for each month then why not just divide F7 by 12.

    Using a divisor of 360 when you're using dates which cover 365 days are always going to result in a difference.

    Regards
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    02-17-2008
    Posts
    70

    Re: monthly split of contract value

    divinding by 12 will not work firstly it only works when dates are matching (manual excercise) and secondly contract date can start from the mid of the month. i am looking for function like days360 which convert 365 days into 360 days.

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,400

    Re: monthly split of contract value

    Hi,

    It's still not clear. Please indicate what values you expect to see each month and maybe we can then come up with a formulaic solution.

    Rgds

  5. #5
    Registered User
    Join Date
    02-17-2008
    Posts
    70

    Re: monthly split of contract value

    it should by 1000 every month

+ Reply to Thread

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