Help me smarty pantzes...

  • Thread starter Thread starter Jerry Pacemaker
  • Start date Start date
J

Jerry Pacemaker

Hey.

If I have a project that lasts 10 days and starts on January 25th and is
worth $1000 can you give me a formula that will calculate how much is earned
in January if we assume that an equal amount is earned each day and that all
days are counted, whether working days or not.

Then a formula that will work out what is earned in February, March, etc.?

The end result would look like this (except in columns)
A1 - Start - 01/25/04
B1 - End - 02/04/04
C1 - Value - $1,000
D1 - Jan - $600
E1 - Feb - $400

I have a millon of these buggers and I need a formula that will break down
revenue into the amount that will be earned in each month based on the start
date and end date.

Thanks,
Shane
 
Jan 25 through Feb 4, inclusive on *both* ends, is 11 days. You are
calculating $100 per day, or 10 days, with $400 = 4 days in February, so I
assume you have a typo here, or you include the end date but not the start
date in the interval. If it's not a typo, see the last paragraph below.

Here's a VBA function that calculates the days in common between two periods.
It is inclusive on *both* ends, i.e. a job that ends on the 1st of the month
calculates as 1 shared day; similarly, a job that starts on the last day of
the month also calculates as 1 shared day.

Let's say
1. you move the job data down to row 2 (i.e. A2:C2)
2. you put month beginning dates starting at D1:E1, so D1 = Jan 1, 2004,
E1 = Feb 1, 2004, .... Dec 1, 2004 in O1
3. you have the Analysis Tool Pak installed so you can use the
EOMONTH function
4. you install the VBA function in a module in the workbook:

Then put this formula in D2 and copy across through O2:

=DaysInCommon($A2,$B2,D$1,EOMONTH(D$1,0))*($B2-$A2+1)*$C2

You can put other jobs in A3:C3, A4:D4, etc. Just copy the formulas down.

If your job dates are inclusive on only 1 end, to exclude the start date, add
1 to it (use $A2+1 instead of $A2); to exclude the end date, subtract 1 from
it ($B2-1 instead of $B2).

You can probably find formulas for this if you search the newsgroup archives
on Google.

Here's the VBA function:

Function DaysInCommon(JobStart, JobEnd, MonthStart, MonthEnd) As Long
Dim First As Date
Dim Last As Date
Dim N As Long

First = JobStart
If MonthStart > First Then First = MonthStart

Last = JobEnd
If MonthEnd < Last Then Last = MonthEnd

N = Last - First + 1 'inclusive on both ends
If N < 0 Then N = 0
DaysInCommon = N

End Function
 
Here's a formula version. It assumes the job start and end dates are both to
be included.

=MAX(MIN($B2,EOMONTH(D$1,0))-MAX($A2,D$1)+1,0)/($B2-$A2+1)*$C2

If it's to be inclusive on only 1 end, in the denominator change ($B2-$A2+1)
to ($B2-$A2). To exclude the start date, in the numerator change $A2 to $A2+1;
to exclude the end date, in the numerator change $B2 to $B2-1.
 
Thank you so much Myrna for the help - this is perfect. Can you send me your
e-mail address to sgclarke(at)eircom(dot)net. I would like to say thanks!

Regards,
Shane
 
No need for any special thanks. BTW, are you going to use the formula or the
VBA function (just curious)?
 
Hi Myrna,

Thank you so much for your help - your formula is perfect.

Can you send your e-mail address to sgclarke(at)eircom(dot)net - I want to
thank you personally.

Regards,
Shane
 
Back
Top