calculate sum of previous 30 days

Question

I have three sheets, sheet1, sheet2, and sheet3.

Sheet 1 have column like : Project, employee name, hours worked, date Sheet 2 have Column like : employee name, rate/hour

So the common between two sheets are employee name.

Sheet 1 will be updating every day, so that sheet will not have define range.

$ spent will be calculated on = Sheet1! hours worked * Sheet2 rate/hour (Here common in both sheet is employee name)

Total $ spent on each project, that i achieved, but i need to know how to calculate $ spent on last 30 days.

Are you calculating the $ for each employee on sheet3 then add then up or directly the total? — Jerry
No, i am calculation each project $ then i will calculate total Project $ in sheet 3, here i will get employee name in sheet1 and sheet2, from sheet1 i will get project name, employee name and hours, from sheet2 i will get employee name, rate/hour, so no project name in sheet2. — user3248817
So you will have the "Project Names" pre populated in Sheet 3? Could the list of employees be across the columns to build a matrix that could be populated? — Ken

CynicalSection CynicalSection · Accepted Answer · 2014-03-11T12:54:51

You can use IF to remove older values from your sum. Something like:

=IF(NOW()-Sheet1!Date <= 30, Sheet1!HoursWorked*Sheet2!Rate,0)