Hi, I work at a college. I am trying to build a form that will replace our
8-copy-carbonless form. The academic calendar is 9 payperiods that do not
start and end with months, but range from 30 to 31 day periods. For this
academic year the start was 08/21/06 and the end is 05/20/07. People start
and end on any day (paid by a 7 day week). Each day of each payperiod would
be a different fraction of the 9.000000 total.
The formula that I am trying to build takes the total salary for 9 months,
divides that by 9 multiplies that 1/9th by the appointment % which gives me
what that person will "actually be paid" for each pay period. Those parts I
have just fine.
This is where I'm stuck. I want to take that "actual pay" and multply it by
the fraction of 9.000000 that the start and end date represent for the total
encumbrance needed.
I have built a lookup table that gives each date it's fraction value.
for example, 08/23/06 = 0.096774 and 09/24/06 = 1.033333. (The first pay
period is 31 days).
So if a person earned $32,000/9=$3,555.56 per pay period with a 100%
appointment and worked from 08/23/06 to 09/24/06 they would earn
$3,555.56*1.033333 or a total of $3,674.07.
Thank You in advance, Jean

I might misunderstand you, but assuming you have the Payperiod salary
in A1, the start date in A2, the end date in A3, your lookup table is
called DatePortion (or whatever) with the fraction value in column 2,
could you not use:

=A1*(vlookup(A3,DatePortion,2,false)-vlookup(A2-1,DatePortion,2,false)

Scott

Jean wrote:
Hi, I work at a college. I am trying to build a form that will replace our
8-copy-carbonless form. The academic calendar is 9 payperiods that do not
start and end with months, but range from 30 to 31 day periods. For this
academic year the start was 08/21/06 and the end is 05/20/07. People start
and end on any day (paid by a 7 day week). Each day of each payperiod would
be a different fraction of the 9.000000 total.
The formula that I am trying to build takes the total salary for 9 months,
divides that by 9 multiplies that 1/9th by the appointment % which gives me
what that person will "actually be paid" for each pay period. Those parts I
have just fine.
This is where I'm stuck. I want to take that "actual pay" and multply it by
the fraction of 9.000000 that the start and end date represent for the total
encumbrance needed.
I have built a lookup table that gives each date it's fraction value.
for example, 08/23/06 = 0.096774 and 09/24/06 = 1.033333. (The first pay
period is 31 days).
So if a person earned $32,000/9=$3,555.56 per pay period with a 100%
appointment and worked from 08/23/06 to 09/24/06 they would earn
$3,555.56*1.033333 or a total of $3,674.07.
Thank You in advance, Jean

Hi Scott, Thank YOU for taking your time to answer my messy (unclear) question.
Your formula has gotten me closer than 4 days of off and on trying.
When I put it in (with my cell info) it told me I was missing a parenthesis.
In my spreadsheet it is Payperiod Salary= I12; Start Date= B12 and End Date=
C12.
My formula looks like this:
=I12*(VLOOKUP(C12,DatePortion,2,FALSE)-(VLOOKUP(B12-1,DatePortion,2,FALSE)

I tried putting in a parenthesis in a few different places...but then I got
#NAME? for an answer. If you can see where I went wrong, please let me know.
Thank You again, Jean
"Scott" wrote:

I might misunderstand you, but assuming you have the Payperiod salary
in A1, the start date in A2, the end date in A3, your lookup table is
called DatePortion (or whatever) with the fraction value in column 2,
could you not use:

=A1*(vlookup(A3,DatePortion,2,false)-vlookup(A2-1,DatePortion,2,false)

Scott

Jean wrote:
Hi, I work at a college. I am trying to build a form that will replace our
8-copy-carbonless form. The academic calendar is 9 payperiods that do not
start and end with months, but range from 30 to 31 day periods. For this
academic year the start was 08/21/06 and the end is 05/20/07. People start
and end on any day (paid by a 7 day week). Each day of each payperiod would
be a different fraction of the 9.000000 total.
The formula that I am trying to build takes the total salary for 9 months,
divides that by 9 multiplies that 1/9th by the appointment % which gives me
what that person will "actually be paid" for each pay period. Those parts I
have just fine.
This is where I'm stuck. I want to take that "actual pay" and multiply it by
the fraction of 9.000000 that the start and end date represent for the total
encumbrance needed.
I have built a lookup table that gives each date it's fraction value.
for example, 08/23/06 = 0.096774 and 09/24/06 = 1.033333. (The first pay
period is 31 days).
So if a person earned $32,000/9=$3,555.56 per pay period with a 100%
appointment and worked from 08/23/06 to 09/24/06 they would earn
$3,555.56*1.033333 or a total of $3,674.07.
Thank You in advance, Jean

You indicated that you had a lookup table with the date and the
fractional value.

I presumed that the date was in the first column and the fractional
value in the second, and than you named this range. :-) You could
substitute the boundaries of this range, ie. if it's on another sheet,
go 'Sheet 2'!$A$2:$B$273. The date has to be in the first column, but
the fractional value can be in any column thereafter, you just have to
adjust the column referenced in the VLOOKUP() formula.

In other words, I just picked a random name "DatePortion" to represent
that range. My apologies for not being clearer. So you can either
name your table something and put that name in the formula, or
substitute the location of your lookup table like the above, namely
'Sheet 2'!$A$2:$B$273 (or wherever it is).

You might have a slight hiccup for the formula I provided as it looks
at the day before your start day for the value. (That is, it assumes
you want the period from B12 to C12 inclusive, so normally you'd
subtract the day before B12) The hiccup would be if B12 was 8/21/06...
you'd need to add 8/20/06 with a fractional value 0 to the beginning of
your lookup table. This may have been an incorrect assumption. I
looked at your data and thought it looked more like you wanted
$3,555.56*(1.03333 - 0.096774).

Hopefully that helps more than it confuses,
Scott

Jean wrote:
Hi Scott, Thank YOU for taking your time to answer my messy (unclear) question.
Your formula has gotten me closer than 4 days of off and on trying.
When I put it in (with my cell info) it told me I was missing a parenthesis.
In my spreadsheet it is Payperiod Salary= I12; Start Date= B12 and End Date=
C12.
My formula looks like this:
=I12*(VLOOKUP(C12,DatePortion,2,FALSE)-(VLOOKUP(B12-1,DatePortion,2,FALSE)

I tried putting in a parenthesis in a few different places...but then I got
#NAME? for an answer. If you can see where I went wrong, please let me know.
Thank You again, Jean
"Scott" wrote:

I might misunderstand you, but assuming you have the Payperiod salary
in A1, the start date in A2, the end date in A3, your lookup table is
called DatePortion (or whatever) with the fraction value in column 2,
could you not use:

=A1*(vlookup(A3,DatePortion,2,false)-vlookup(A2-1,DatePortion,2,false)

Scott

Jean wrote:
Hi, I work at a college. I am trying to build a form that will replace our
8-copy-carbonless form. The academic calendar is 9 payperiods that do not
start and end with months, but range from 30 to 31 day periods. For this
academic year the start was 08/21/06 and the end is 05/20/07. People start
and end on any day (paid by a 7 day week). Each day of each payperiod would
be a different fraction of the 9.000000 total.
The formula that I am trying to build takes the total salary for 9 months,
divides that by 9 multiplies that 1/9th by the appointment % which gives me
what that person will "actually be paid" for each pay period. Those parts I
have just fine.
This is where I'm stuck. I want to take that "actual pay" and multiply it by
the fraction of 9.000000 that the start and end date represent for the total
encumbrance needed.
I have built a lookup table that gives each date it's fraction value.
for example, 08/23/06 = 0.096774 and 09/24/06 = 1.033333. (The first pay
period is 31 days).
So if a person earned $32,000/9=$3,555.56 per pay period with a 100%
appointment and worked from 08/23/06 to 09/24/06 they would earn
$3,555.56*1.033333 or a total of $3,674.07.
Thank You in advance, Jean

Hi Scott. Between you and God I am almost there! Thank You for again taking
your time to help me. I am such a novice at building formulas. It wasn't
that you weren't clear in your 1st answer...I just don't know what I am
looking at a lot of the times! You broke it down so well here that I am able
to understand what I am looking at and that is light years away from where I
was yesterday before your first response. Thank You Again, Jean

"Scott" wrote:

You indicated that you had a lookup table with the date and the
fractional value.

I presumed that the date was in the first column and the fractional
value in the second, and than you named this range. :-) You could
substitute the boundaries of this range, ie. if it's on another sheet,
go 'Sheet 2'!$A$2:$B$273. The date has to be in the first column, but
the fractional value can be in any column thereafter, you just have to
adjust the column referenced in the VLOOKUP() formula.

In other words, I just picked a random name "DatePortion" to represent
that range. My apologies for not being clearer. So you can either
name your table something and put that name in the formula, or
substitute the location of your lookup table like the above, namely
'Sheet 2'!$A$2:$B$273 (or wherever it is).

You might have a slight hiccup for the formula I provided as it looks
at the day before your start day for the value. (That is, it assumes
you want the period from B12 to C12 inclusive, so normally you'd
subtract the day before B12) The hiccup would be if B12 was 8/21/06...
you'd need to add 8/20/06 with a fractional value 0 to the beginning of
your lookup table. This may have been an incorrect assumption. I
looked at your data and thought it looked more like you wanted
$3,555.56*(1.03333 - 0.096774).

Hopefully that helps more than it confuses,
Scott

Jean wrote:
Hi Scott, Thank YOU for taking your time to answer my messy (unclear) question.
Your formula has gotten me closer than 4 days of off and on trying.
When I put it in (with my cell info) it told me I was missing a parenthesis.
In my spreadsheet it is Payperiod Salary= I12; Start Date= B12 and End Date=
C12.
My formula looks like this:
=I12*(VLOOKUP(C12,DatePortion,2,FALSE)-(VLOOKUP(B12-1,DatePortion,2,FALSE)

I tried putting in a parenthesis in a few different places...but then I got
#NAME? for an answer. If you can see where I went wrong, please let me know.
Thank You again, Jean
"Scott" wrote:

I might misunderstand you, but assuming you have the Payperiod salary
in A1, the start date in A2, the end date in A3, your lookup table is
called DatePortion (or whatever) with the fraction value in column 2,
could you not use:

=A1*(vlookup(A3,DatePortion,2,false)-vlookup(A2-1,DatePortion,2,false)

Scott

Jean wrote:
Hi, I work at a college. I am trying to build a form that will replace our
8-copy-carbonless form. The academic calendar is 9 payperiods that do not
start and end with months, but range from 30 to 31 day periods. For this
academic year the start was 08/21/06 and the end is 05/20/07. People start
and end on any day (paid by a 7 day week). Each day of each payperiod would
be a different fraction of the 9.000000 total.
The formula that I am trying to build takes the total salary for 9 months,
divides that by 9 multiplies that 1/9th by the appointment % which gives me
what that person will "actually be paid" for each pay period. Those parts I
have just fine.
This is where I'm stuck. I want to take that "actual pay" and multiply it by
the fraction of 9.000000 that the start and end date represent for the total
encumbrance needed.
I have built a lookup table that gives each date it's fraction value.
for example, 08/23/06 = 0.096774 and 09/24/06 = 1.033333. (The first pay
period is 31 days).
So if a person earned $32,000/9=$3,555.56 per pay period with a 100%
appointment and worked from 08/23/06 to 09/24/06 they would earn
$3,555.56*1.033333 or a total of $3,674.07.
Thank You in advance, Jean