Results 1 to 10 of 10
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    calc $ for future dates (Excel 2003)

    Attached is a sample workbook for what should be an easy thing to do but I'm just staring at it frozen.

    A given $ amount (revenue) is projected to be received between 90-270 days after some start date. The revenue will be evenly distribute over that 3 month time period (33.3%) and any dates before and after that time period are $0. How can I calc which cells get the revenue and which don't. I don't want to use NETWORKDAYS since I can't get the users to always install it beforehand. I was using DAYS360 and think I need to AND it with the range of dates but get stuck on how to tell it to stop after 270 days.

    <pre>Start Date 1/1/2008 2/1/2008 3/1/2008 4/1/2008 5/1/2008 6/1/2008 7/1/2008
    2/21/2008 $1000 $0 $0 $0 $333.3 $333.33 $333.33 $0</pre>

    Hopefull the sample I included is more clear.

    Thnx, Deb
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: calc $ for future dates (Excel 2003)

    I don't understand - 90 to 270 days is a 6 month period, not a 3 month period. In your example, revenue starts 2 months after the start date (approx. 60 days) and stops 4 months after the start date (approx. 120 days). In the sample workbook, it is 3 months (approx. 90 days) and 5 months (approx. 150 days). <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: calc $ for future dates (Excel 2003)

    How about in E9:
    =IF($B9>G$8,0,(MAX(0,MIN(DATEDIF($B9,G$8,"d"),$I$3-1)-$I$2)/($I$3-$I$2-1)-SUM($D9:F9)/$C9)*$C9)

    Copy this to E9:P13

    This doesn't give the answers you have (due to all the questions Hans raised), but it does the 90-270 that you asked for. You can adjust those numbers in the cells I3 and I2...

    Steve

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: calc $ for future dates (Excel 2003)

    <P ID="edit" class=small>(Edited by sdckapr on 13-Dec-07 09:47. Modified the formula to use the START of the Month in Launch date to keep the "90 days")</P>To get the numbers in your table, you can use in E9
    =IF($B9>E$8,0,(MAX(0,MIN(DATEDIF(DATE(YEAR($B9),MO NTH($B9),1),E$8,"m"),($I$3-1)/30)-$I$2/30+1)/(($I$3-$I$2-1)/30+1)-SUM($D99)/$C9)*$C9)

    Copy this to E9:P13

    You can keep the 90 in I2
    But in I3 you must use: 151 since the revenue stops after 5 months not 9 months (you only generate revenue for 3 months)

    The formula I use works in MONTHS rather than days (as in the earlier post), so you could change I2 and I3 to reflect months and adjust the formula by removing the "30"s.

    Steve

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calc $ for future dates (Excel 2003)

    Yes I really messed up this explanation. I haven't yet reviewed the other replies to see who is the best mind reader (which was the goal of this question apparently). <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22>

    From a given start date, I want the projected revenue to be distributed across a 1 quarter period (3 months). The revenue starts coming in 90 days from this start date and ends 3 months after (so 90 days + 90 days = 180 days, not the 270 I listed). I don't bother to get fancy by taking into account the number of days left in the month and show the revenue for the # of days left, but just assume it all starts coming in on the first. Our fiscal year starts in August so we're now in FY08 (Q1=08/09/10, Q2=11/12/01, Q3=02/03/04, Q4=05/06/07).

    <pre>Revenue = $500,000, 33.33% per month for 3 month period
    Start Date Dec07 Jan08 Feb08 Mar 08 Apr 08 May08 Jun08
    12/12/07 $0 $0 $0 $165K $165K $165K $0
    </pre>


    Ideally I'd like to take into account the fact that the revenue starts on 3/12/08 not 3/01/08 as I show here but one thing at a time. If I did that I'd need to only show the % of $165K for the remaining days in March (3/12 thru 3/31). But I'm not going to bother to deal with that now, we're not using this for accounting purposes.

    I'll look over the other replies to see if they'll work.

    Thnx, Deb

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: calc $ for future dates (Excel 2003)

    <P ID="edit" class=small>(Edited by sdckapr on 13-Dec-07 18:25. Added a "monthly" formula, rather than a daily

    (Edited by sdckapr on 13-Dec-07 15:47. Corrected a mistake in formula)

    )</P>Thhis should work (in E9) if you really want to go with "days" and start with the actual day. Note it can spread into 4 months due to the start and end being "partial months" and the days of the months averaging more than 30 days [This would be what I interpret you want as "ideally"

    =IF($B9>E$8,0,(MAX(0,MIN(DATEDIF($B9,DATE(YEAR(E$8 ),MONTH(E$8)+1,0),"d"),$I$3-1)-$I$2)/($I$3-$I$2-1)-SUM($D99)/$C9)*$C9)

    This will not spread the money evely within each month. To do this you should work with months and not days...

    Steve
    PS: if you want equal amounts in each month and you want to enter something like 90, 181 then you can use in E9:
    =IF($B9>H$8,0,(MAX(0,MIN(DATEDIF(DATE(YEAR($B9),MO NTH($B9),1),H$8,"m"),($I$3-1)/30-1)-$I$2/30+1)/(($I$3-$I$2-1)/30)-SUM($D9:G9)/$C9)*$C9)

  7. #7
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calc $ for future dates (Excel 2003)

    Ok I tried to implement the suggestions but am still stuck. I'm happy to just do the calc assuming full 30 day months so each month as the same $. Attached is the same sample file with my attempts at using your formulas. Can you review this again and find where I messed it up?

    Table 1 is 90 days to 180 days, and table 2 uses 3 months to 6 months for the range to show the revenue.

    Thnx, Deb
    Attached Files Attached Files

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: calc $ for future dates (Excel 2003)

    How about these:

    In E9:
    =IF($B9>E$8,0,(MAX(0,MIN(DATEDIF(DATE(YEAR($B9),MO NTH($B9),1),E$8,"m"),$I$3-1)-$I$2+1)/($I$3-$I$2)-SUM($D99)/$C9)*$C9)
    Copy E9:P13

    In E27:
    =IF($B27>E$26,0,(MAX(0,MIN(DATEDIF($B27,DATE(YEAR( E$26),MONTH(E$26)+1,0)-1,"d"),$A$22)-$A$21)/($A$22-$A$21)-SUM($D2727)/$C27)*$C27)
    Copy to E27:P31



    Steve

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: calc $ for future dates (Excel 2003)

    In <post#=682,533>post 682,533</post#> I had 2 options. The first uses months and has equal amounts in each month. The "LaunchDate is presumed to be the first day of the month in the listed "LaunchDate". Thus you always get in 3 months one-third of the total. [This matches your current calculations]

    The second uses the actual "LaunchDate" and the days listed. In this the amount in each month will depend on the number of days in the month and it will be spread among 4 months: a partial start and end month and 2 full months (if you use $90 for each one, you can see how the full months have the number of days...). This is what you seem to describe as "ideal" using the actual dates...

    Another option that came to me this morning is a compromise to those 2 options. It involves using equal amounts in each full month (not related to the actual number of days and presuming 30 days / month in the "revenuedays"). One can base how much the start and end "partial month" is based on the fraction of the month the "LaunchDate" starts in

    In this scheme the values from the calculations in Section 1 and Section2 will be the same. There will generally be 4 months with values: 2 partials and 2 full month. Each full month will get the same amount (not related to the number of days) and the partial will be related to how much after the start of the month the "LaunchDate" is.

    In E9:
    =IF($B9>E$8,0,(MAX(0,MIN(DATEDIF($B9,E$8,"m")+(1-DAY($B9)/DAY(DATE(YEAR($B9),MONTH($B9)+1,0))),$I$3-1)-$I$2+1)/($I$3-$I$2)-SUM($D99)/$C9)*$C9)

    Copy E9:P13

    In E27:
    =IF($B27>E$8,0,(MAX(0,MIN(DATEDIF($B27,E$8,"m")+(1-DAY($B27)/DAY(DATE(YEAR($B27),MONTH($B27)+1,0))),$A$22/30-1)-$A$21/30+1)/(($A$22-$A$21)/30)-SUM($D2727)/$C27)*$C27)

    Copy to E27:P31

    The differences in the 2 formulas are the difference in the cells for the revenue start/stop and also in the second the start/stop days are divided by 30 to get the number of months of each. These formulas uses the months between the (actual) "LaunchDate" and the start of the month and (since DATEDIF uses only whole months) adds to that the fraction of the month remaining in the "LaunchDate" to add the "first month"

    Perhaps this is what you had more in mind for "ideal"...
    Steve

  10. #10
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calc $ for future dates (Excel 2003)

    WOW this is amazing, it works beautifully and is way way more than I could of done <img src=/S/bouncenburn.gif border=0 alt=bouncenburn width=31 height=31>. I get so completely lost in all those nested statements although I understand the logic in breaking it down in days and subtracting the left over from 30. I have many great Excel books but still could never of figured this out on my own. I really need to spend more time breaking these problems down and piecing the formula together.

    Thank you so much for taking the time to nail this for me. <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

    // Deb

Posting Permissions

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