Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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? |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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? |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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? |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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? |
#5
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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? |
#6
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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? |
#7
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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? |
#8
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
macro to change date ranges | Excel Discussion (Misc queries) | |||
formula to look up and return smallest date from a range of dates | Excel Worksheet Functions | |||
Return Sundays date of current week | Excel Worksheet Functions | |||
How do I chart date ranges with varying start and finish dates? | Charts and Charting in Excel | |||
Return the end of month date from a date | Excel Worksheet Functions |