Results 1 to 10 of 10

20071213, 05:18 #1
 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 90270 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

20071213, 09:05 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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>

20071213, 15:16 #3
 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$31)$I$2)/($I$3$I$21)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 90270 that you asked for. You can adjust those numbers in the cells I3 and I2...
Steve

20071213, 16:47 #4
 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 13Dec07 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$31)/30)$I$2/30+1)/(($I$3$I$21)/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

20071213, 21:29 #5
 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

20071214, 01:25 #6
 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 13Dec07 18:25. Added a "monthly" formula, rather than a daily
(Edited by sdckapr on 13Dec07 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$31)$I$2)/($I$3$I$21)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$31)/301)$I$2/30+1)/(($I$3$I$21)/30)SUM($D9:G9)/$C9)*$C9)

20071214, 01:40 #7
 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

20071214, 02:32 #8
 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$31)$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

20071214, 13:42 #9
 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 onethird 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")+(1DAY($B9)/DAY(DATE(YEAR($B9),MONTH($B9)+1,0))),$I$31)$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")+(1DAY($B27)/DAY(DATE(YEAR($B27),MONTH($B27)+1,0))),$A$22/301)$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

20071214, 20:43 #10
 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