How to return a value between date ranges

I have one worksheet with 52 rows for each week of the year with the
following headings: week #, start date of week and end date of week. I have
another worksheet where I need to forecast expected expenses for new
employees based on the date they start ie. Joe starts in Feb 6 (week 6) and
then calculate expected spend based on remaining weeks in the year. I do
this manually right now. How can I automate this?

Mary-lou,

try the tips on this web,

http://www.bettersolutions.com/excel...M012916331.htm

regards from Brazil
Marcelo

"Mary-Lou" escreveu:

> I have one worksheet with 52 rows for each week of the year with the
> following headings: week #, start date of week and end date of week. I have
> another worksheet where I need to forecast expected expenses for new
> employees based on the date they start ie. Joe starts in Feb 6 (week 6) and
> then calculate expected spend based on remaining weeks in the year. I do
> this manually right now. How can I automate this?

Thanks for the tip - but I searched through a lot of examples and haven't
found what I'm looking for yet.

Here's what I'm trying to do:

Worksheet A would have the following:
Resource Start Date Rate Expected Expenses
Joe 03/16/06 $100
Mary 04/05/06 $75

1) In the Expected Expenses column, I would calculate the expected cost for
a resource from the time they start until the end of company fiscal year (not
calendar year).

2) I would manually look up the Start Date in Worksheet B (see below) to
see what week# the start date falls under.

3) Then I would manually add the week # into the Expected Expenses
calculation.

I'm looking for a way to automate taking Start Date from Worksheet A and
looking it up in Worksheet B in order to return the week #.


Worksheet B - Company fiscal calendar by week
Week # Start of Week End of Week
1 10/22/05 10/28/05
2 10/29/05 11/04/05
….
52 10/14/06 10/20/06


"Marcelo" wrote:

> Mary-lou,
>
> try the tips on this web,
>
> http://www.bettersolutions.com/excel...M012916331.htm
>
> regards from Brazil
> Marcelo
>
> "Mary-Lou" escreveu:
>
> > I have one worksheet with 52 rows for each week of the year with the
> > following headings: week #, start date of week and end date of week. I have
> > another worksheet where I need to forecast expected expenses for new
> > employees based on the date they start ie. Joe starts in Feb 6 (week 6) and
> > then calculate expected spend based on remaining weeks in the year. I do
> > this manually right now. How can I automate this?

If I understand, one way to do it is:

Worksheet B, create another column (maybe D) after then the end week date
with the number of week, than you could use a formula like

=(52-vlookup(start date,worksheetb!$b$2:$d$53,3,1))*rate

Change start date and rate to the cells reference as your convenience.

hope it helps
Regards
Marcelo



"Mary-Lou" escreveu:

> Thanks for the tip - but I searched through a lot of examples and haven't
> found what I'm looking for yet.
>
> Here's what I'm trying to do:
>
> Worksheet A would have the following:
> Resource Start Date Rate Expected Expenses
> Joe 03/16/06 $100
> Mary 04/05/06 $75
>
> 1) In the Expected Expenses column, I would calculate the expected cost for
> a resource from the time they start until the end of company fiscal year (not
> calendar year).
>
> 2) I would manually look up the Start Date in Worksheet B (see below) to
> see what week# the start date falls under.
>
> 3) Then I would manually add the week # into the Expected Expenses
> calculation.
>
> I'm looking for a way to automate taking Start Date from Worksheet A and
> looking it up in Worksheet B in order to return the week #.
>
>
> Worksheet B - Company fiscal calendar by week
> Week # Start of Week End of Week
> 1 10/22/05 10/28/05
> 2 10/29/05 11/04/05
> ….
> 52 10/14/06 10/20/06
>
>
> "Marcelo" wrote:
>
> > Mary-lou,
> >
> > try the tips on this web,
> >
> > http://www.bettersolutions.com/excel...M012916331.htm
> >
> > regards from Brazil
> > Marcelo
> >
> > "Mary-Lou" escreveu:
> >
> > > I have one worksheet with 52 rows for each week of the year with the
> > > following headings: week #, start date of week and end date of week. I have
> > > another worksheet where I need to forecast expected expenses for new
> > > employees based on the date they start ie. Joe starts in Feb 6 (week 6) and
> > > then calculate expected spend based on remaining weeks in the year. I do
> > > this manually right now. How can I automate this?

Sorry to be pest, but I'm not having luck with this. Doesn't vlookup expect
a match?

Worksheet B has 52 rows (plus the header) containing the week number (1 thru
52) and the date range of each week of the 52 weeks (start date of week and
end date of week - no dates in between).

Worksheet A is sorted by resource name, not start date and contains hundreds
of names in it - but I'm only interested in calculating the expected expenses
for anyone hired within the current fiscal year.

So basically, if someone starts sometime in May, I need to see which date
range their start date would fall into (currently I manually look at the
range in Worksheet B) in order to identify the week # they started in and
apply that week # into a calculation in Worksheet A with the resource row.

"Marcelo" wrote:

> If I understand, one way to do it is:
>
> Worksheet B, create another column (maybe D) after then the end week date
> with the number of week, than you could use a formula like
>
> =(52-vlookup(start date,worksheetb!$b$2:$d$53,3,1))*rate
>
> Change start date and rate to the cells reference as your convenience.
>
> hope it helps
> Regards
> Marcelo
>
>
>
> "Mary-Lou" escreveu:
>
> > Thanks for the tip - but I searched through a lot of examples and haven't
> > found what I'm looking for yet.
> >
> > Here's what I'm trying to do:
> >
> > Worksheet A would have the following:
> > Resource Start Date Rate Expected Expenses
> > Joe 03/16/06 $100
> > Mary 04/05/06 $75
> >
> > 1) In the Expected Expenses column, I would calculate the expected cost for
> > a resource from the time they start until the end of company fiscal year (not
> > calendar year).
> >
> > 2) I would manually look up the Start Date in Worksheet B (see below) to
> > see what week# the start date falls under.
> >
> > 3) Then I would manually add the week # into the Expected Expenses
> > calculation.
> >
> > I'm looking for a way to automate taking Start Date from Worksheet A and
> > looking it up in Worksheet B in order to return the week #.
> >
> >
> > Worksheet B - Company fiscal calendar by week
> > Week # Start of Week End of Week
> > 1 10/22/05 10/28/05
> > 2 10/29/05 11/04/05
> > ….
> > 52 10/14/06 10/20/06
> >
> >
> > "Marcelo" wrote:
> >
> > > Mary-lou,
> > >
> > > try the tips on this web,
> > >
> > > http://www.bettersolutions.com/excel...M012916331.htm
> > >
> > > regards from Brazil
> > > Marcelo
> > >
> > > "Mary-Lou" escreveu:
> > >
> > > > I have one worksheet with 52 rows for each week of the year with the
> > > > following headings: week #, start date of week and end date of week. I have
> > > > another worksheet where I need to forecast expected expenses for new
> > > > employees based on the date they start ie. Joe starts in Feb 6 (week 6) and
> > > > then calculate expected spend based on remaining weeks in the year. I do
> > > > this manually right now. How can I automate this?

Mary-lou, sorry if I was not clear

imagine:

Worksheet A

A B C D
1 Resource Sart date Rate Expected Expenses
2 Joe 16/03/06 100,00 3.100,00
3 Mary 05/04/06 75,00 2.100,00
4 Jonh 07/10/06 125,00 125,00

Worksheet B

A B C D
1 Week# Start date End date Week #
2 1 22/10/05 28/10/05 1
3 2 29/10/05 04/11/05 2
...
53 52 14/10/06 20/10/06 52

Formula on expected expenses:

=(52-VLOOKUP(B2,WOOKRBOOKB!$B$2:$D$53,3,1))*c2

Note that the last "1" of the formula order to vlookup to find in each range
of date is the start date that you are looking for, you do not need to have
all dates on the B workbbok.

for eg. if your star date is 07/19/06 vlookup will return week 39 because it
is on the range between 07/15/06 and 07/21/06; as they lack 13 weeks to
finish the fiscal period of 52 weeks, we you have 52-39 = 13 * rate.

hope it helps
regards from Brazil
Marcelo






"Mary-Lou" escreveu:

> Sorry to be pest, but I'm not having luck with this. Doesn't vlookup expect
> a match?
>
> Worksheet B has 52 rows (plus the header) containing the week number (1 thru
> 52) and the date range of each week of the 52 weeks (start date of week and
> end date of week - no dates in between).
>
> Worksheet A is sorted by resource name, not start date and contains hundreds
> of names in it - but I'm only interested in calculating the expected expenses
> for anyone hired within the current fiscal year.
>
> So basically, if someone starts sometime in May, I need to see which date
> range their start date would fall into (currently I manually look at the
> range in Worksheet B) in order to identify the week # they started in and
> apply that week # into a calculation in Worksheet A with the resource row.
>
> "Marcelo" wrote:
>
> > If I understand, one way to do it is:
> >
> > Worksheet B, create another column (maybe D) after then the end week date
> > with the number of week, than you could use a formula like
> >
> > =(52-vlookup(start date,worksheetb!$b$2:$d$53,3,1))*rate
> >
> > Change start date and rate to the cells reference as your convenience.
> >
> > hope it helps
> > Regards
> > Marcelo
> >
> >
> >
> > "Mary-Lou" escreveu:
> >
> > > Thanks for the tip - but I searched through a lot of examples and haven't
> > > found what I'm looking for yet.
> > >
> > > Here's what I'm trying to do:
> > >
> > > Worksheet A would have the following:
> > > Resource Start Date Rate Expected Expenses
> > > Joe 03/16/06 $100
> > > Mary 04/05/06 $75
> > >
> > > 1) In the Expected Expenses column, I would calculate the expected cost for
> > > a resource from the time they start until the end of company fiscal year (not
> > > calendar year).
> > >
> > > 2) I would manually look up the Start Date in Worksheet B (see below) to
> > > see what week# the start date falls under.
> > >
> > > 3) Then I would manually add the week # into the Expected Expenses
> > > calculation.
> > >
> > > I'm looking for a way to automate taking Start Date from Worksheet A and
> > > looking it up in Worksheet B in order to return the week #.
> > >
> > >
> > > Worksheet B - Company fiscal calendar by week
> > > Week # Start of Week End of Week
> > > 1 10/22/05 10/28/05
> > > 2 10/29/05 11/04/05
> > > ….
> > > 52 10/14/06 10/20/06
> > >
> > >
> > > "Marcelo" wrote:
> > >
> > > > Mary-lou,
> > > >
> > > > try the tips on this web,
> > > >
> > > > http://www.bettersolutions.com/excel...M012916331.htm
> > > >
> > > > regards from Brazil
> > > > Marcelo
> > > >
> > > > "Mary-Lou" escreveu:
> > > >
> > > > > I have one worksheet with 52 rows for each week of the year with the
> > > > > following headings: week #, start date of week and end date of week. I have
> > > > > another worksheet where I need to forecast expected expenses for new
> > > > > employees based on the date they start ie. Joe starts in Feb 6 (week 6) and
> > > > > then calculate expected spend based on remaining weeks in the year. I do
> > > > > this manually right now. How can I automate this?

This works! I use vlookup quite a bit - but didn't realize you could do
ranges. This is awesome. Thanks for all your help.

"Marcelo" wrote:

> Mary-lou, sorry if I was not clear
>
> imagine:
>
> Worksheet A
>
> A B C D
> 1 Resource Sart date Rate Expected Expenses
> 2 Joe 16/03/06 100,00 3.100,00
> 3 Mary 05/04/06 75,00 2.100,00
> 4 Jonh 07/10/06 125,00 125,00
>
> Worksheet B
>
> A B C D
> 1 Week# Start date End date Week #
> 2 1 22/10/05 28/10/05 1
> 3 2 29/10/05 04/11/05 2
> ...
> 53 52 14/10/06 20/10/06 52
>
> Formula on expected expenses:
>
> =(52-VLOOKUP(B2,WOOKRBOOKB!$B$2:$D$53,3,1))*c2
>
> Note that the last "1" of the formula order to vlookup to find in each range
> of date is the start date that you are looking for, you do not need to have
> all dates on the B workbbok.
>
> for eg. if your star date is 07/19/06 vlookup will return week 39 because it
> is on the range between 07/15/06 and 07/21/06; as they lack 13 weeks to
> finish the fiscal period of 52 weeks, we you have 52-39 = 13 * rate.
>
> hope it helps
> regards from Brazil
> Marcelo
>
>
>
>
>
>
> "Mary-Lou" escreveu:
>
> > Sorry to be pest, but I'm not having luck with this. Doesn't vlookup expect
> > a match?
> >
> > Worksheet B has 52 rows (plus the header) containing the week number (1 thru
> > 52) and the date range of each week of the 52 weeks (start date of week and
> > end date of week - no dates in between).
> >
> > Worksheet A is sorted by resource name, not start date and contains hundreds
> > of names in it - but I'm only interested in calculating the expected expenses
> > for anyone hired within the current fiscal year.
> >
> > So basically, if someone starts sometime in May, I need to see which date
> > range their start date would fall into (currently I manually look at the
> > range in Worksheet B) in order to identify the week # they started in and
> > apply that week # into a calculation in Worksheet A with the resource row.
> >
> > "Marcelo" wrote:
> >
> > > If I understand, one way to do it is:
> > >
> > > Worksheet B, create another column (maybe D) after then the end week date
> > > with the number of week, than you could use a formula like
> > >
> > > =(52-vlookup(start date,worksheetb!$b$2:$d$53,3,1))*rate
> > >
> > > Change start date and rate to the cells reference as your convenience.
> > >
> > > hope it helps
> > > Regards
> > > Marcelo
> > >
> > >
> > >
> > > "Mary-Lou" escreveu:
> > >
> > > > Thanks for the tip - but I searched through a lot of examples and haven't
> > > > found what I'm looking for yet.
> > > >
> > > > Here's what I'm trying to do:
> > > >
> > > > Worksheet A would have the following:
> > > > Resource Start Date Rate Expected Expenses
> > > > Joe 03/16/06 $100
> > > > Mary 04/05/06 $75
> > > >
> > > > 1) In the Expected Expenses column, I would calculate the expected cost for
> > > > a resource from the time they start until the end of company fiscal year (not
> > > > calendar year).
> > > >
> > > > 2) I would manually look up the Start Date in Worksheet B (see below) to
> > > > see what week# the start date falls under.
> > > >
> > > > 3) Then I would manually add the week # into the Expected Expenses
> > > > calculation.
> > > >
> > > > I'm looking for a way to automate taking Start Date from Worksheet A and
> > > > looking it up in Worksheet B in order to return the week #.
> > > >
> > > >
> > > > Worksheet B - Company fiscal calendar by week
> > > > Week # Start of Week End of Week
> > > > 1 10/22/05 10/28/05
> > > > 2 10/29/05 11/04/05
> > > > ….
> > > > 52 10/14/06 10/20/06
> > > >
> > > >
> > > > "Marcelo" wrote:
> > > >
> > > > > Mary-lou,
> > > > >
> > > > > try the tips on this web,
> > > > >
> > > > > http://www.bettersolutions.com/excel...M012916331.htm
> > > > >
> > > > > regards from Brazil
> > > > > Marcelo
> > > > >
> > > > > "Mary-Lou" escreveu:
> > > > >
> > > > > > I have one worksheet with 52 rows for each week of the year with the
> > > > > > following headings: week #, start date of week and end date of week. I have
> > > > > > another worksheet where I need to forecast expected expenses for new
> > > > > > employees based on the date they start ie. Joe starts in Feb 6 (week 6) and
> > > > > > then calculate expected spend based on remaining weeks in the year. I do
> > > > > > this manually right now. How can I automate this?

thanks for the feedback
regards
Marcelo

"Mary-Lou" escreveu:

> This works! I use vlookup quite a bit - but didn't realize you could do
> ranges. This is awesome. Thanks for all your help.
>
> "Marcelo" wrote:
>
> > Mary-lou, sorry if I was not clear
> >
> > imagine:
> >
> > Worksheet A
> >
> > A B C D
> > 1 Resource Sart date Rate Expected Expenses
> > 2 Joe 16/03/06 100,00 3.100,00
> > 3 Mary 05/04/06 75,00 2.100,00
> > 4 Jonh 07/10/06 125,00 125,00
> >
> > Worksheet B
> >
> > A B C D
> > 1 Week# Start date End date Week #
> > 2 1 22/10/05 28/10/05 1
> > 3 2 29/10/05 04/11/05 2
> > ...
> > 53 52 14/10/06 20/10/06 52
> >
> > Formula on expected expenses:
> >
> > =(52-VLOOKUP(B2,WOOKRBOOKB!$B$2:$D$53,3,1))*c2
> >
> > Note that the last "1" of the formula order to vlookup to find in each range
> > of date is the start date that you are looking for, you do not need to have
> > all dates on the B workbbok.
> >
> > for eg. if your star date is 07/19/06 vlookup will return week 39 because it
> > is on the range between 07/15/06 and 07/21/06; as they lack 13 weeks to
> > finish the fiscal period of 52 weeks, we you have 52-39 = 13 * rate.
> >
> > hope it helps
> > regards from Brazil
> > Marcelo
> >
> >
> >
> >
> >
> >
> > "Mary-Lou" escreveu:
> >
> > > Sorry to be pest, but I'm not having luck with this. Doesn't vlookup expect
> > > a match?
> > >
> > > Worksheet B has 52 rows (plus the header) containing the week number (1 thru
> > > 52) and the date range of each week of the 52 weeks (start date of week and
> > > end date of week - no dates in between).
> > >
> > > Worksheet A is sorted by resource name, not start date and contains hundreds
> > > of names in it - but I'm only interested in calculating the expected expenses
> > > for anyone hired within the current fiscal year.
> > >
> > > So basically, if someone starts sometime in May, I need to see which date
> > > range their start date would fall into (currently I manually look at the
> > > range in Worksheet B) in order to identify the week # they started in and
> > > apply that week # into a calculation in Worksheet A with the resource row.
> > >
> > > "Marcelo" wrote:
> > >
> > > > If I understand, one way to do it is:
> > > >
> > > > Worksheet B, create another column (maybe D) after then the end week date
> > > > with the number of week, than you could use a formula like
> > > >
> > > > =(52-vlookup(start date,worksheetb!$b$2:$d$53,3,1))*rate
> > > >
> > > > Change start date and rate to the cells reference as your convenience.
> > > >
> > > > hope it helps
> > > > Regards
> > > > Marcelo
> > > >
> > > >
> > > >
> > > > "Mary-Lou" escreveu:
> > > >
> > > > > Thanks for the tip - but I searched through a lot of examples and haven't
> > > > > found what I'm looking for yet.
> > > > >
> > > > > Here's what I'm trying to do:
> > > > >
> > > > > Worksheet A would have the following:
> > > > > Resource Start Date Rate Expected Expenses
> > > > > Joe 03/16/06 $100
> > > > > Mary 04/05/06 $75
> > > > >
> > > > > 1) In the Expected Expenses column, I would calculate the expected cost for
> > > > > a resource from the time they start until the end of company fiscal year (not
> > > > > calendar year).
> > > > >
> > > > > 2) I would manually look up the Start Date in Worksheet B (see below) to
> > > > > see what week# the start date falls under.
> > > > >
> > > > > 3) Then I would manually add the week # into the Expected Expenses
> > > > > calculation.
> > > > >
> > > > > I'm looking for a way to automate taking Start Date from Worksheet A and
> > > > > looking it up in Worksheet B in order to return the week #.
> > > > >
> > > > >
> > > > > Worksheet B - Company fiscal calendar by week
> > > > > Week # Start of Week End of Week
> > > > > 1 10/22/05 10/28/05
> > > > > 2 10/29/05 11/04/05
> > > > > ….
> > > > > 52 10/14/06 10/20/06
> > > > >
> > > > >
> > > > > "Marcelo" wrote:
> > > > >
> > > > > > Mary-lou,
> > > > > >
> > > > > > try the tips on this web,
> > > > > >
> > > > > > http://www.bettersolutions.com/excel...M012916331.htm
> > > > > >
> > > > > > regards from Brazil
> > > > > > Marcelo
> > > > > >
> > > > > > "Mary-Lou" escreveu:
> > > > > >
> > > > > > > I have one worksheet with 52 rows for each week of the year with the
> > > > > > > following headings: week #, start date of week and end date of week. I have
> > > > > > > another worksheet where I need to forecast expected expenses for new
> > > > > > > employees based on the date they start ie. Joe starts in Feb 6 (week 6) and
> > > > > > > then calculate expected spend based on remaining weeks in the year. I do
> > > > > > > this manually right now. How can I automate this?