Retrieve data based on a data date falling in specified date range

[marks9172]

I am building a payroll budgeting tool and need to return a daily regular and daily overtime wage for each day of the year based on whether the associated wage falls within a specified date range.

The spreadsheet is attached and I took a shot at several formulas and could never get it to work properly.

The formulas reside on the Daily Wages sheet in cells K2 and L2. The specific purpose of these formulas is to retrieve data from the Fixed Wages sheet.

For the Daily Regular Fixed Wages column on the Daily Wages sheet I need to return the total from G3 to G52 on the Fixed Wages sheet for all values in that column whose Regular Start Date to Regular End Date range (columns D and E on the Fixed Wages sheet) includes the date from column G on the Daily Wages sheet on the same row as a the cell containing the formula.
For example, a formula in cell K2 on the Daily Wages sheet has an associated date in G2 of 7/1/2015. The formula in K2 on the Daily Wages sheet should look at columns D and E on the Fixed Wages sheet. If 7/1/2015 falls within the range of any of the dates listed in columns D and E on the Fixed Wages sheet, the formula should return the total Regular Daily Wages from column G on the Fixed Wages sheet for all instances where the date range includes 7/1/2015. Since the data contains only one instance of the a date range including 7/1/2015, the total returned should be 85.71.
If we were to use the same formula in cell K187 on the Daily Wages sheet, the associated date is 1/2/2016. The formula should return a total of 596.73 since all ranges on the Fixed Payroll sheet columns D and E include 1/2/2016 in their range.

I can use the same formula with different column references for the overtime wages as well.

Thanks!Payroll Budget Master - 2015-2016-1.xlsx

[bebo021999]

In K2:

Please Login or Register  to view this content.

[marks9172]

Thanks bebo021999, worked great. I did have to modify the last instance of $G$2 to be $G2 and it did exactly what I needed!
Much appreciated!