Help with Date Range Formula

[MythicZohar]

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

 

[Ron Rosenfeld]

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

 

[MythicZohar]

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

 

[MythicZohar]

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

 

[Ron Rosenfeld]

I will try you recommendation to see what I come up with.. Thank Again

You're welcome. Let me know.
--ron

 

[MythicZohar]

You're welcome.  Let me know.
--ron

Worked out perfectly.. Man I love excel !!! Thanks Ron

 

[Ron Rosenfeld]

Worked out perfectly.. Man I love excel !!! Thanks Ron

You're welcome. Glad I could help. Thanks for the feedback.
--ron