Distribute Project Value over entire project life
Results 1 to 4 of 4

Thread: Distribute Project Value over entire project life
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Distribute Project Value over entire project life

    Good Day

    I have to calculate the per day amount for a project and put it in a report showing it on an annual basis. My issue comes if the project starts mid of the month, i need a formula to determine the date from project start to year end and calculate per day rate.

    Eg:
    Project Start 18/12/2019
    Project End 19/08/2022
    Project Budget 100,000
    Project rate per day will be (project end date) - (project start date) / project budget = 102.5 per day.

    Now my report headers will be:
    YEAR 2019| YEAR 2020| YEAR 2021|YEAR 2022|YEAR 2023 < - should be formula to show contract start year and end years based on Project Start & Project End years (optional)

    In year 2019, formula should calculate 18/12/2019 to 31/12/2019 * 102.5 = 13days * 102.5
    In year 2020, formula should calculate 01/01/2020 to 31/12/2020 * 102.5 = 365days * 102.5
    In year 2021, formula should calculate 01/01/2021 to 31/12/2021 * 102.5 = 365days * 102.5
    In year 2020, formula should calculate 01/01/2022 to 19/08/2022 * 102.5 = 230days * 102.5
    In year 2023, formula should identify 01/01/2023 and Determine this date doesn't fall in contract period and return '0' value
    total should tally with the Project Budge = 100,000

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,404
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Distribute Project Value over entire project life

    See if this is any use. D2 & D3 formulas copied across.
    Note that my formula produces a slightly different value for 2020 and 2021. You said you wanted a 'per day' amount and 2020 has 366 days whereas 2021 only has 365, hence the difference.

    Spread

    ABCDEFGHIJK
    1StartEndBudgetYEAR 2019YEAR 2020YEAR 2021YEAR 2022YEAR 2023YEAR 2024YEAR 2025TOTAL
    2 2019202020212022202320242025
    318/12/201919/08/2022100,0001,333.3337,538.4637,435.9023,692.310.000.000.00100,000.00

    Spreadsheet Formulas
    CellFormula
    D2=RIGHT(D1,4)+0
    D3=MAX(MIN(DATE(D$2,12,31)-$A3,DATE(D$2,12,31)-DATE(D$2,1,1)+1,$B3-DATE(D$2,1,1)+1,$B3-$A3+1),0)*$C3/($B3-$A3)
    K3=SUM(D3:J3)


    Excel tables to the web >> Excel Jeanie HTML 4
    Last edited by Peter_SSs; Jul 28th, 2019 at 08:01 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    New Member
    Join Date
    Sep 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Distribute Project Value over entire project life

    Thank you Mr. Excel!! Works perfectly :D

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,404
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Distribute Project Value over entire project life

    Quote Originally Posted by rodmic View Post
    Thank you Mr. Excel!! Works perfectly :D
    You're welcome.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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