asked on

Determine value if a date is with two date

Hello, I need to determine the Annual Salary at the time a bonus was given. There's a bonus date and a sal begin and end date. If the bonus date falls between the sal begin and end date then I need the Annual Date reflected in Column E for each EEID. Please see attached example.

There are two sets of data
1) EEID, Date of Bonus and bonus amount
2) EEID, Sal Beg Date, Sal End Date and Annual Salary Amount
compensation2.xlsx

ASKER CERTIFIED SOLUTION

Wayne Taylor (webtubbs)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Don VonderBurg

Use the AND function with IF

=IF(AND(B2>=I2,B2<=J2),K2,0)

Open in new window

Rob Henson

I believe you can use this formula:

=SUMIFS($K:$K,$H:$H,$A2,$I:$I,"<="&$B2,$J:$J,">="&$B2)

However, that gives a different result for the first entry (as does Wayne's suggestion) but I believe our results are correct as the $210,000 does not correspond with the dates.

EEID 1 was earning $194,828 between 03/03/07 and 29/02/08 and the bonus was on 29/02/08 so should have result of $194,828

Thanks
Rob

SOLUTION

Audra Breedlove

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Audra Breedlove

ASKER

Thank you both so much. Sorry for taking so long to close this ticket.

Rob Henson

You have marked your own comment as Assisted.

Can I ask that you Request Attention to reassign points?

Thanks