# Thread: Distribute total working hours to three shifts Thanks: 0 Likes: 0

1. ## Distribute total working hours to three shifts

Hi all!

I want to distribute (divide) total working hours of an employee to shifts. There are three shifts: first - from 6 AM to 2 PM, second -from 2 PM to 10 PM, and third from 10 PM to 6 AM

simple example:

- on may 7th, The employee worked from 7 AM to 7 PM, it has to be devided to 7 hours of first shift, and 5 hours of second shift.

not so simple example

- employee started work on may 7th, 7 PM and finished on may 8th at 7 AM. His working hours are: 3 hours of second shift on the may 7th, 2 hours of third shift on may 7th, 6 hours on third shift may8 th and 1 hour of first shift on may 8th.

Also, I will have to deal with the weekends and holidays, which are paid different.

How can I accomplish this? I have tried with median function, but as I understands it can not deal with everything.

Thanks a lot

Neven

2. ## Re: Distribute total working hours to three shifts

Originally Posted by nmiskulin
- employee started work on may 7th, 7 PM and finished on may 8th at 7 AM. His working hours are: 3 hours of second shift on the may 7th, 2 hours of third shift on may 7th, 6 hours on third shift may8 th and 1 hour of first shift on may 8th.
You need to include an example of your entries.

For the worker above, does the spreadsheet record look like this:
 Date In Out 2018-05-07 19:00 07:00

Or does it look like this:
 In Out 2018-05-07 19:00 2018-05-08 07:00

For the worker above, are all the worked hours credited to May 7?

For your purposes, May 7th starts at 2018-05-07 06:00 and May 7th ends (usually) at 2018-05-08 06:00?

3. ## Re: Distribute total working hours to three shifts

Originally Posted by thisoldman
You need to include an example of your entries.

For the worker above, does the spreadsheet record look like this:
 Date In Out 2018-05-07 19:00 07:00

Or does it look like this:
 In Out 2018-05-07 19:00 2018-05-08 07:00

For the worker above, are all the worked hours credited to May 7?

For your purposes, May 7th starts at 2018-05-07 06:00 and May 7th ends (usually) at 2018-05-08 06:00?
First of all, tnanks for the reply. I will send you a example sheet tomorow. Is that ok?

OK.

5. ## Re: Distribute total working hours to three shifts

Originally Posted by thisoldman
OK.
How can I send yoiu a word file?

Neven

6. ## Re: Distribute total working hours to three shifts

Before you send me anything, be aware that I will repost the anonymized information in the forum.

7. ## Re: Distribute total working hours to three shifts

I understand, how can I attach some files?
Neven

8. ## Re: Distribute total working hours to three shifts

If you can reduce the spreadsheet to say six rows by perhaps ten columns, you can copy and directly paste into a forum post.

You can use an Excel add-in, see https://www.mrexcel.com/forum/about-board/508133-attachments-post2507729.html#post2507729

I sent you one of my email addresses in a PM. You can send the file to me if the other methods fail.

9. ## Re: Distribute total working hours to three shifts

The current timesheet is a table in a a Word file, docx format. This is an image of it.

Here are the first few rows of the table, after copying it to Excel:

ABCDEFGHIJKLMNOPQRSTUVWXY
3RegularSaturdaySundayHoliday M-FHoliday SatHoliday Sun
4DateWork startWork end1st shift2nd shift 3rd shift 1st shift2nd shift3rd shift 1st shift2nd shift3rd shift 1st shift2nd shift3rd shift 1st shift2nd shift3rd shift 1st shift2nd shift3rd shift VacationSick leaveOVERTIMETOTAL
512345678910111213141516171819202122232425
61
7271975
831932
94716
105
116
12771975

Sheet1

The copy I made, as an xlsx, is available at: https://www.dropbox.com/s/j4x9qrd724...heet.xlsx?dl=0

The timesheet is currently manually filled.

More in next post.

10. ## Re: Distribute total working hours to three shifts

From an email sent to me:

Originally Posted by nmiskulin
It's in word format, and my goal is to make an excel version (automatic calculation of hours, which is now done by hand)

The first column contains day of the month. Marked blue are the saturday & sunday and purple are marked holidays. I suppose the saturday and sunday can be identified by WEEKDAY function. As for holidays (Christmas, Easter, national holidays), it would be ideal to have a cell in which they could be listed, if they exist in a current month.

The only thing the employee has to enter is work start (column 2) and work end (column 3), and vacation leave and sick leave if they had any (columns 22 and 23)

Done by hand is calculation (in red font) how many hours in which shift is done. This is entered in columns 4 - 6 for mon-fri, columns 7-9 for saturday, columns 10-12 for sunday, columns 13-15 for holiday that occours on mon-fri, columns 16-18 for holiday that occours on saturday and columns 19-21 for holiday that occours on sunday). This is because there are different pay rates for certain day, holidays etc.

My biggest problem, among everything else, is how to deal with hours (night shift) that "spills" into another day...

## User Tag List

#### Posting Permissions

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