College Assignment Woes

[Scottie318]

Hello,

I would GREATLY appreciate it if someone could guide me in some kind of
direction. I have been given the following data:

On a new sheet, named Salary Table, starting at cell A1, build the
following table:

Name
Current
Salary
Percent Increase
Effective Month
2004 Compensation.

My exact problem is that I must createa formula that can be used for
all to calculate the amount to each employee for the year 2004. The
increases take place on the first day of the month specified. This
means that if increase comes in July, the pay is six months at the old
rate and six months at the new rate. Therefore the 2004 compensation
must take into account the fact that part of the year is at the old
salary and part of the year is at the new salary. I understand the
problem up to this point. What I do NOT understand is how to use the
=Vlookup function to convert text month to a number for the calculation
of effective date. It also states months must be in ascending sequence
in the table.

Thanks
Scottie

 

[Harald Staff]

Hi Scottie

On a blank sheet:
Enter July in A1. To get month number in B1:
=MONTH(DATEVALUE(A1&" 1"))
Should return 7. To convert 7 back to july in C1:
=TEXT(DATE(2003,B1,1),"mmmm")

Hope this get you started. Best wishes Harald
Followup to newsgroup only please.

 

[Nick Hodge]

Scottie

Presuming in

A1 = Nick Hodge
B1 = 25000
C1 = 6%
D1 = July
E1 =

=((B2/12)*(MONTH(DATEVALUE("01
"&D2)-1)))+(((B2/12)+((B2/12)*C2))*(MONTH(DATEVALUE("01 "&D2)-1)))