I have my employee schedule in a excel sheet. It is a weekly schedule that doesn't change week by week, but changes day by day within a week. Every Monday, Tuesday, etc. is the same schedule. In the file are days for Monday, Tuesday, etc.. I don't know if this possible, but I would like to utilize excel to calculate a employees total expected hours automatically. I however have no idea how to do this in excel and am not exactly sure how.

Question

I have my employee schedule in a excel sheet. It is a weekly schedule that doesn't change week by week, but changes day by day within a week. Every Monday, Tuesday, etc. is the same schedule. In the file are days for Monday, Tuesday, etc.. I don't know if this possible, but I would like to utilize excel to calculate a employees total expected hours automatically. I however have no idea how to do this in excel and am not exactly sure how.

Let me try and explain this. So lets say cell A7B9 was Monday 3:00 P.M. - 7:00 P.M., the name in the cell was John Smith, the numerical value associated with the cell (that I would have to have predefined I assume some how) would be 4 (the total time of the shift). This would be done for every shift on the schedule. The system would then sum the total time for all shifts associated with a particular employee to get the employees expected weekly hour total for each employee.

Is there a way to do this in excel or perhaps another program which I can write a function like this?

Basically I want to be able to automatically calculate the total expected hours of all my employees in a week off of a schedule in an excel sheet. Cell include employees name if they are working the shift.

Thanks for any help!!!

Answer 1

I know there's a way to do this, and it has to do with formulas and linking them from one worksheet to another. Read widely:

http://www.google.com/search?q=excel+formulas+link+from+one+sheet+to+another&oq=excel+formulas+link+&aqs=chrome.1.57j0l3.10365j0&sourceid=chrome&ie=UTF-8

Answer 2

Yes, it is definitely possible to achieve this in Excel using formulas. Here's how you can accomplish it:

1. First, you need to define the durations associated with each shift. For example, if a shift is from 3:00 PM to 7:00 PM, the duration is 4 hours. You can create a separate table or define named ranges for these durations.

2. In your Excel sheet, make sure that each shift is entered in a consistent format across rows and columns. For example, if the schedule for Monday is in cell A7:B9, the employee's name is in cell A7, and the shift time is in cell B7.

3. In a separate column, let's say column C, you can use the VLOOKUP function to look up the specific shift time in your defined duration table and calculate the duration for that shift. For example, if the duration table is in the range D1:E4, you can enter the following formula in cell C7: =VLOOKUP(B7, D1:E4, 2, FALSE)

4. Drag this formula down for all the shifts in your schedule.

5. Finally, you can use the SUMIF function to calculate the total expected hours for each employee over a week. Let's say the employee names are in column A, and the durations are in column C. You can use the formula =SUMIF(A:A, "John Smith", C:C) to calculate the total expected hours for John Smith.

6. Repeat step 5 for each employee, adjusting the name accordingly.

By following these steps, you should be able to automatically calculate the total expected hours for each employee based on their shifts in the Excel sheet. Remember to adjust the cell references and formulas to fit your specific setup.

Alternatively, you can also achieve this using other programming languages such as Python or JavaScript if you prefer to write a custom program to handle the calculations.