Formulas based on date

[Cliff Partridge]

I want to model revenue projections for salespeople and I'm having
some trouble.


A B C D E F
1 Revenue
2 Compensation Hire Date 2005 2006 2007 2008
3 $100K 06/01/2005 $8,333 $158,333 $258,333 $300,000
4


Revenue Variables:
First 6 months = 0% of Comp
Second 6 months = 100% of Comp
Second 12 months = 200% of Comp
Third 12 months + = 300% of Comp

I want formulas in cells C3:F3 to calculate the revenue based on hire
date and revenue variables.
(I entered the correct revenue numbers for the example in row 4 to
help with formula creation)
As you can see in row 3, the 2005 calculation will have 6 months of no
revenue and 1 month of revenue at 100% of the person's total
compensation. 2006 will have 5 months at 100%, and 7 months at 200%
of comp. Then 2007 will be 5 months at 200% of comp, and 7 months at
300% of comp. Finally 2008 will be at 300% of compensation.

I would like the formulas to work no matter what Hire Date is entered
in year 2005-2008.

I've tried several variables of DATEDIF and IF formulas to no avail.
I get stuck when I have to pick up a partial period like the example
above (calculating how much of a variable period was calculated
last year etc.); if everything was a neat 6 months all the time, it
would be pretty easy.

Thanks for your help.

cp

 

[Ron Rosenfeld]

I want to model revenue projections for salespeople and I'm having
some trouble.


A B C D E F
1 Revenue
2 Compensation Hire Date 2005 2006 2007 2008
3 $100K 06/01/2005 $8,333 $158,333 $258,333 $300,000
4


Revenue Variables:
First 6 months = 0% of Comp
Second 6 months = 100% of Comp
Second 12 months = 200% of Comp
Third 12 months + = 300% of Comp

I want formulas in cells C3:F3 to calculate the revenue based on hire
date and revenue variables.
(I entered the correct revenue numbers for the example in row 4 to
help with formula creation)
As you can see in row 3, the 2005 calculation will have 6 months of no
revenue and 1 month of revenue at 100% of the person's total
compensation. 2006 will have 5 months at 100%, and 7 months at 200%
of comp. Then 2007 will be 5 months at 200% of comp, and 7 months at
300% of comp. Finally 2008 will be at 300% of compensation.

I would like the formulas to work no matter what Hire Date is entered
in year 2005-2008.

I've tried several variables of DATEDIF and IF formulas to no avail.
I get stuck when I have to pick up a partial period like the example
above (calculating how much of a variable period was calculated
last year etc.); if everything was a neat 6 months all the time, it
would be pretty easy.

Thanks for your help.

cp

This formula only counts full months. So if your employee were hired on
6/2/2005 instead of 6/1/2005, he would have zero for the year 2005. And during
2006 he would have 6 months at 100% and 6 months at 200%.


First set up a Lookup table someplace on your worksheet. Name the Table 'tbl'
and set it up like this:

0 0% 0%
6 0% 100%
12 100% 200%
24 200% 300%
36 300% 300%


With your Data table set up:

Compensation Hire Date 2005 2006 2007 2008 2009
$100,000 1/2/2005

Enter the formula in C2 and copy/drag across as far as necessary.

=((12-MOD(DATEDIF(Hire_Date,DATE(C$1+1,1,1),"m"),12))*
VLOOKUP(DATEDIF(Hire_Date,DATE(C$1+1,1,1),"m"),Tbl,2)+
MOD(DATEDIF(Hire_Date,DATE(C$1+1,1,1),"m")-VLOOKUP(
DATEDIF(Hire_Date,DATE(C$1+1,1,1),"m"),Tbl,1),12)*VLOOKUP(
DATEDIF(Hire_Date,DATE(C$1+1,1,1),"m"),Tbl,3))/12*Compensation

If your 'years' are in a different row, you will need to alter C$1 to reflect
that.

Compensation and Hire_Date are named ranges also (A2:An and B2:Bn in mine)




--ron

 

[Cliff Partridge]

This formula only counts full months. So if your employee were hired on
6/2/2005 instead of 6/1/2005, he would have zero for the year 2005. And during
2006 he would have 6 months at 100% and 6 months at 200%.


First set up a Lookup table someplace on your worksheet. Name the Table 'tbl'
and set it up like this:

0 0% 0%
6 0% 100%
12 100% 200%
24 200% 300%
36 300% 300%


With your Data table set up:

Compensation Hire Date 2005 2006 2007 2008 2009
$100,000 1/2/2005

Enter the formula in C2 and copy/drag across as far as necessary.

=((12-MOD(DATEDIF(Hire_Date,DATE(C$1+1,1,1),"m"),12))*
VLOOKUP(DATEDIF(Hire_Date,DATE(C$1+1,1,1),"m"),Tbl,2)+
MOD(DATEDIF(Hire_Date,DATE(C$1+1,1,1),"m")-VLOOKUP(
DATEDIF(Hire_Date,DATE(C$1+1,1,1),"m"),Tbl,1),12)*VLOOKUP(
DATEDIF(Hire_Date,DATE(C$1+1,1,1),"m"),Tbl,3))/12*Compensation

If your 'years' are in a different row, you will need to alter C$1 to reflect
that.

Compensation and Hire_Date are named ranges also (A2:An and B2:Bn in mine)




--ron

This seems to work unless the person starts on January first. For
example, I have someone making $175K starting 1/1/05, so the revenue
for year 1 should be $87.5K (first 6 months at 0%, second 6 months at
100%). Instead the formula returns a full $175K for year 1.

cp

 

[Ron Rosenfeld]

This seems to work unless the person starts on January first. For
example, I have someone making $175K starting 1/1/05, so the revenue
for year 1 should be $87.5K (first 6 months at 0%, second 6 months at
100%). Instead the formula returns a full $175K for year 1.

Darn, I thought I had checked that. I guess not.

I'll have to review it but I won't have time for a day or two.


--ron

 

[Ron Rosenfeld]

This seems to work unless the person starts on January first. For
example, I have someone making $175K starting 1/1/05, so the revenue
for year 1 should be $87.5K (first 6 months at 0%, second 6 months at
100%). Instead the formula returns a full $175K for year 1.

I'm sure there must be a more elegant method, but I think the following works:

=((12-MOD(DATEDIF(Hire_Date,DATE(C$1+1,1,1),"m"),12))*
VLOOKUP(DATEDIF(Hire_Date,DATE(C$1+1,1,1),"m"),Tbl,2)*
(1-0.5*(YEAR(Hire)=C$1))+
MOD(DATEDIF(Hire_Date,DATE(C$1+1,1,1),"m")-VLOOKUP(
DATEDIF(Hire_Date,DATE(C$1+1,1,1),"m"),Tbl,1),12)*VLOOKUP(
DATEDIF(Hire_Date,DATE(C$1+1,1,1),"m"),Tbl,3))/12*Compensation

I made the first year a special case.


--ron