Break Revenue Down By Month - Start End Date and Amount
Results 1 to 3 of 3

Thread: Break Revenue Down By Month - Start End Date and Amount
Thanks Thanks: 0 Likes Likes: 0

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

    Default Break Revenue Down By Month - Start End Date and Amount

    Hello,

    I have two dates and an amount field, I want to break the revenue into each month. I have a formula

    =(IF(AND($B2>=D$1,$A2<=DATE(YEAR(D$1),MONTH(D$1)+1,0)),MIN($B2,DATE(YEAR(D$1),MONTH(D$1)+1,1))-MAX($A2,D$1),0)/($B2-$A2))*$C2

    It breaks down the revenue incorrectly across the months. I took the value and look at it daily and it was slightly off. The total amount does match up, just not how its weighted into each month.

    Any idea why?

    Start Date End Date Amount 4/1/2018 5/1/2018 6/1/2018 7/1/2018 8/1/2018 9/1/2018 10/1/2018 11/1/2018 12/1/2018 1/1/2019 2/1/2019 3/1/2019 4/1/2019 5/1/2019 6/1/2019 7/1/2019
    5/18/2018 6/15/2018 4250 $0 $2,125 $2,125 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
    11/19/2018 12/16/2018 0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
    1/14/2019 5/5/2019 25200 $0 $0 $0 $0 $0 $0 $0 $0 $0 $4,086 $6,357 $7,038 $6,811 $908 $0 $0
    1/9/2019 2/17/2019 22500 $0 $0 $0 $0 $0 $0 $0 $0 $0 $13,269 $9,231 $0 $0 $0 $0 $0
    1/9/2019 2/17/2019 22500 $0 $0 $0 $0 $0 $0 $0 $0 $0 $13,269 $9,231 $0 $0 $0 $0 $0
    1/7/2019 12/30/2019 49800 $0 $0 $0 $0 $0 $0 $0 $0 $0 $3,487 $3,906 $4,324 $4,185 $4,324 $4,185 $4,324

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,225
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Break Revenue Down By Month - Start End Date and Amount

    Maybe...

    =IF(AND($B2>=D$1,$A2<=DATE(YEAR(D$1),MONTH(D$1)+1,0)),MIN($B2,EOMONTH(D$1,0))-MAX($A2,D$1-1),0)*$C2/($B2-$A2)

    M.

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

    Default Re: Break Revenue Down By Month - Start End Date and Amount

    Thanks for trying Marcelo but still doesn't work. The months don't match up to if I do it daily.

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
  •