Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Date Range Formula
I am helping with a spreadsheet and have had little success in trying
to come up with a solution. Here's the problem. I will need the ability to have the formula reference a range of dates based on a max for each date of incident and return and answer. Here's and an example: Lets say I have 3 entries Name date of incident Average Monthly Wage John Doe 01/01/2008 4333. Jane Doe 01/01/2003 2888 Sally Doe 01/01/2009 3600 In the example above I would need to have the average monthly wage column reference a range or table that included a series of date ranges that set a max average monthly wage depending on the date range which coincides with the date of incident, and return the actual wage if not greater than the max for the period below is an example of the table the formula would reference if date of injury is: 01/01/2008-12/31/2008 max average monthly wage = 1385 01/01/1979-12/31/2003 max average monthly wage = 2000 01/01/2009-12/31/2009 max average monthly wage=3600 Thanks in advance |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Date Range Formula
On Sun, 25 Jan 2009 15:11:02 -0800 (PST), MythicZohar
wrote: I am helping with a spreadsheet and have had little success in trying to come up with a solution. Here's the problem. I will need the ability to have the formula reference a range of dates based on a max for each date of incident and return and answer. Here's and an example: Lets say I have 3 entries Name date of incident Average Monthly Wage John Doe 01/01/2008 4333. Jane Doe 01/01/2003 2888 Sally Doe 01/01/2009 3600 In the example above I would need to have the average monthly wage column reference a range or table that included a series of date ranges that set a max average monthly wage depending on the date range which coincides with the date of incident, and return the actual wage if not greater than the max for the period below is an example of the table the formula would reference if date of injury is: 01/01/2008-12/31/2008 max average monthly wage = 1385 01/01/1979-12/31/2003 max average monthly wage = 2000 01/01/2009-12/31/2009 max average monthly wage=3600 Thanks in advance The setup of your wage table is critical to answering this question. Is the wage table continuous and orderly? Or is it discontinuous and disordered as you show? If it is continuous, you could use a simple VLOOKUP function: e.g. with date of incident in B1 =vlookup(b1,wage_table,2) Depending on your wage_table and your requirements, you might need to test for out of range incident dates. The wage table should be set up like this, with the Period_start_dates in ascending order, and entered as normal excel dates. Period_Start_date Wage 1/1/79 2000 1/1/08 1385 1/1/09 3600 If the wage table is, in fact, discontinuous and out of order, does each period start at the beginning of a year? If so, then you could use something like: =VLOOKUP(DATE(YEAR(B2),1,1),wage_table,2,FALSE) --ron |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Date Range Formula
On Jan 25, 6:17*pm, Ron Rosenfeld wrote:
On Sun, 25 Jan 2009 15:11:02 -0800 (PST), MythicZohar wrote: I am helping with *a spreadsheet and have had little success in trying to come up with a solution. Here's the problem. I will need the ability to have the formula reference a range of dates based on a max for each date of incident and return and answer. Here's and an example: Lets say I have 3 entries Name * * * * * *date of incident * * * * Average Monthly Wage John Doe * * * *01/01/2008 * * * * * * * * * 4333. Jane Doe * * * *01/01/2003 * * * * * * * * * 2888 Sally Doe * * * *01/01/2009 * * * * * * * * * 3600 In the example above I would need to have the average monthly wage column *reference a range or table that *included *a series of date ranges that set a max average monthly wage depending on the date range which coincides with the date of incident, and return the actual wage if not greater than the max for the period below is an example of the table the formula would reference if date of injury is: 01/01/2008-12/31/2008 max average monthly wage = 1385 01/01/1979-12/31/2003 max average monthly wage = 2000 01/01/2009-12/31/2009 max average monthly wage=3600 Thanks in advance The setup of your wage table is critical to answering this question. Is the wage table continuous and orderly? *Or is it discontinuous and disordered as you show? If it is continuous, you could use a simple VLOOKUP function: e.g. with date of incident in B1 =vlookup(b1,wage_table,2) Depending on your wage_table and your requirements, you might need to test for out of range incident dates. The wage table should be set up like this, with the Period_start_dates in ascending order, and entered as normal excel dates. Period_Start_date * * * Wage 1/1/79 * * * * * * * * *2000 1/1/08 * * * * * * * * *1385 1/1/09 * * * * * * * * *3600 If the wage table is, in fact, discontinuous and out of order, does each period start at the beginning of a year? If so, then you could use something like: =VLOOKUP(DATE(YEAR(B2),1,1),wage_table,2,FALSE) --ron Ron: Thanks for your reply,The wage table would be in ascending order dates and amounts. But the data that will be recorded would vary depending on the date of the incident I would only need the table to return the amt if the answer exceeding the max for the yr.. I will try your recommendation after work today.. Thanks again Date of Injury AMW New Hourly Wage Total Earning Capacity AMW @WAGE 12/23/1985 1800 25 4333.00 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Date Range Formula
On Jan 26, 6:14*am, MythicZohar wrote:
On Jan 25, 6:17*pm, Ron Rosenfeld wrote: On Sun, 25 Jan 2009 15:11:02 -0800 (PST), MythicZohar wrote: I am helping with *a spreadsheet and have had little success in trying to come up with a solution. Here's the problem. I will need the ability to have the formula reference a range of dates based on a max for each date of incident and return and answer. Here's and an example: Lets say I have 3 entries Name * * * * * *date of incident * * * * Average Monthly Wage John Doe * * * *01/01/2008 * * * * * * * * * 4333. Jane Doe * * * *01/01/2003 * * * * * * * * * 2888 Sally Doe * * * *01/01/2009 * * * * * * * * * 3600 In the example above I would need to have the average monthly wage column *reference a range or table that *included *a series of date ranges that set a max average monthly wage depending on the date range which coincides with the date of incident, and return the actual wage if not greater than the max for the period below is an example of the table the formula would reference if date of injury is: 01/01/2008-12/31/2008 max average monthly wage = 1385 01/01/1979-12/31/2003 max average monthly wage = 2000 01/01/2009-12/31/2009 max average monthly wage=3600 Thanks in advance The setup of your wage table is critical to answering this question. Is the wage table continuous and orderly? *Or is it discontinuous and disordered as you show? If it is continuous, you could use a simple VLOOKUP function: e.g. with date of incident in B1 =vlookup(b1,wage_table,2) Depending on your wage_table and your requirements, you might need to test for out of range incident dates. The wage table should be set up like this, with the Period_start_dates in ascending order, and entered as normal excel dates. Period_Start_date * * * Wage 1/1/79 * * * * * * * * *2000 1/1/08 * * * * * * * * *1385 1/1/09 * * * * * * * * *3600 If the wage table is, in fact, discontinuous and out of order, does each period start at the beginning of a year? If so, then you could use something like: =VLOOKUP(DATE(YEAR(B2),1,1),wage_table,2,FALSE) --ron Ron: Thanks for your reply,The wage table would be in ascending order dates and amounts. But the data that will be recorded would vary depending on the date of the incident I would only need the table to return the amt if the answer exceeding the max for the yr.. I will try your recommendation after work today.. Thanks again Date of Injury *AMW * * *New Hourly Wage * * * * Total Earning Capacity * * * * *AMW @WAGE 12/23/1985 * * * *1800 * * * * * * 25 4333.00 Sorry, I was editing my reply on and accidentally posted prematurely using the space bar.. who knew.. I will try you recommendation to see what I come up with.. Thank Again |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Date Range Formula
On Mon, 26 Jan 2009 05:18:52 -0800 (PST), MythicZohar
wrote: I will try you recommendation to see what I come up with.. Thank Again You're welcome. Let me know. --ron |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Date Range Formula
On Jan 29, 5:57*pm, Ron Rosenfeld wrote:
On Mon, 26 Jan 2009 05:18:52 -0800 (PST), MythicZohar wrote: I will try you recommendation to see what I come up with.. Thank Again You're welcome. *Let me know. --ron Worked out perfectly.. Man I love excel !!! Thanks Ron |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Date Range Formula
On Wed, 18 Feb 2009 20:11:38 -0800 (PST), MythicZohar
wrote: Worked out perfectly.. Man I love excel !!! Thanks Ron You're welcome. Glad I could help. Thanks for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need a formula to determine if date falls within a date range then | Excel Worksheet Functions | |||
Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
If formula for date range | Excel Discussion (Misc queries) |