Employee Hours Report with multiple rates and categories

[DuNandi]

Hey there,

I am trying to create a a file that will serve as time log and dashboard report for consultants who work a maximum of two hours a day, with a strict(fixed) calendar schedule with multiple sessions. Some consultants may work one hour of a particular schedule on category A(Lead) and the second hour as category 2 (assist). I am having a difficult time figuring out how to create a sheet that sums the rates for all the categories worked on a given day without using a macro. I'm not sure if that is possible or if i'm dreading creating a macro since it is my strength. Here is my sample data

Pay Categories:
Lead
Assistant
Admin

Pay Rates
Lead-$75/hr
Assistant - $40/hr
Admin - $20/hr

Sessions and Session Dates sample:
Session 1 - 10/8/2013 to 11/8/2013
Session 2 - 11/15/2013 to 12/15/2013
Session 3 - 01/06/2014 to 03/15/2014

*Note the dates will or should be listed in separate columns in order for them to be used for daily logging.

Employees (Sample)
Jane Doe
Jack Doe
John Doe

Desire goals of the report:
1. Daily log as employees work e.g if Jane Doe works on 10/8/2013 as a Lead and Assistant, ideally the data entry person should be able to enter these two values on one cell or pick the relevant value from a set list
2. The report would then match the value of the data in the cell for Jane Doe on 10/8/2013 with a table array with categories in one column and the pay rate in another, and return the appropriate pay value which can either be a sum of the two rates or the total hard coded into the cell.
3.Be able to create a dashboard report that would sum the totals by employee, month,sessions etc in a separate sheet vs other criteria such a budget etc

Let me know if my request is vague and i'll be more than happy to expand and explain in further detail.

Regards
DN

 

[bruderbell]

DN,
Have you tried vlookup formulas and pivot tables? Give those a try and post back with what you have and where you are stuck. I don't know that your first desire can be done in a single cell. I can easily envision a data table where you enter name, project, date, hours and whatever else, and then you use a pivot table to see how much people made in any time period or on a project, etc.

I'll watch for your update(s).