Employee Hours Report with multiple rates and categories

DuNandi

New Member
Joined
Jan 29, 2009
Messages
9
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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

bruderbell

Active Member
Joined
Aug 29, 2006
Messages
374
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,122,980
Messages
5,599,160
Members
414,295
Latest member
Dolenhil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top