# Calculate total hours worked in week by worker name

1. ## Calculate total hours worked in week by worker name

Hello, i would like to know how to calculate the time that every worker have in a week?
there is a schedule, workers are called user1, user2,user3...
everyone work different time a week, so i would like to calculate and put the time they worked in weekly hours table by their names.

thank you

example photo:Untitled-2.jpg

2. ## Re: Calculate total hours worked in week by worker name

this is the link to file: 3 times w.excelforum.com/attachment.php?attachmentid=627613&stc=1&d=1560020114

3. ## Re: Calculate total hours worked in week by worker name

What results are you expecting in the yellow cells? Give your manually calculated results.

4. ## Re: Calculate total hours worked in week by worker name

Weekly hours =
user1 total week hours (day1 total hours + day2 total hours .....day7...)
user2 (day1 total hours + day2 total hours .....day7...)
....

in the picture example user3 worked at day2 from 7:15 to 16:15 and in day6 from 7:15 to 16:15,
so in table weekly hours user3 will show: 18

5. ## Re: Calculate total hours worked in week by worker name

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

6. ## Re: Calculate total hours worked in week by worker name

in this example user3 worked at Monday from 7:15 to 16:15 and in Friday from 7:15 to 16:15,
so user 3 total weekly hours will show: 18

attached the schedule, thank you

7. ## Re: Calculate total hours worked in week by worker name

Try U8 in array formula {=SUMPRODUCT((B3:N16=S8)*INDEX(IF(NOT(ISERROR(B4:N17-C4:O17)),B4:N17-C4:O17,0),0))} and copy down and change the time format to TIME

--------------------------------------------------------------------------------
Please click on the small star icon below the post if the response helps

8. ## Re: Calculate total hours worked in week by worker name

Thank you for help

but nothing happens when i put it in u8 (it doesnt give me the result)

9. ## Re: Calculate total hours worked in week by worker name

Withdrawn by FR.

10. ## Re: Calculate total hours worked in week by worker name

Did you try array entering Alvin Chung's formula? --- Ctrl + Shift + Enter

This is a little shorter. Same idea. It still requires Ctrl + Shift + Enter.
Formula:
`Please Login or Register  to view this content.`

11. ## Re: Calculate total hours worked in week by worker name

Attachment 628556

i tried but the answer is wrong
all the shifts set to "time"

12. ## Re: Calculate total hours worked in week by worker name

From your screen shot you seem to be expecting a integer hours. If you are not aware of it times are decimal fractions of a day. The formatting is cosmetic. 16/24 of a day is 0.666666666666667. Formatted for time [hh]:mm = 16:00 or 04:00 PM.

In U10 the underlying value is 0.75. That is 18:00 when formatted to time.

Does that help?

Edit I forgot that the formatting for time needs to have square brackets around the hours to display hours > 24. [hh]:mm

If so

 U 8 28:45 9 09:00 10 18:00 11 25:30 12 26:00 13 27:45 14 15:00 15 10:30

13. ## Re: Calculate total hours worked in week by worker name

Thank you guys, working well.

14. ## Re: Calculate total hours worked in week by worker name

Good. You are welcome. Thank you for the feedback.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1