Page 1 of 2 12 LastLast
Results 1 to 10 of 14

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

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular
    Join Date
    Jan 2014
    Location
    Pittsburgh
    Posts
    1,073
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Distribute total working hours to three shifts

    Quote Originally Posted by nmiskulin View Post
    - 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?
    Last edited by thisoldman; May 7th, 2018 at 09:33 AM.

  3. #3
    New Member
    Join Date
    May 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Distribute total working hours to three shifts

    Quote Originally Posted by thisoldman View Post
    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?

  4. #4
    Board Regular
    Join Date
    Jan 2014
    Location
    Pittsburgh
    Posts
    1,073
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Distribute total working hours to three shifts

    OK.

  5. #5
    New Member
    Join Date
    May 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Distribute total working hours to three shifts

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

    Neven

  6. #6
    Board Regular
    Join Date
    Jan 2014
    Location
    Pittsburgh
    Posts
    1,073
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default 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. #7
    New Member
    Join Date
    May 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Distribute total working hours to three shifts

    I understand, how can I attach some files?
    Neven

  8. #8
    Board Regular
    Join Date
    Jan 2014
    Location
    Pittsburgh
    Posts
    1,073
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default 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. #9
    Board Regular
    Join Date
    Jan 2014
    Location
    Pittsburgh
    Posts
    1,073
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default 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. #10
    Board Regular
    Join Date
    Jan 2014
    Location
    Pittsburgh
    Posts
    1,073
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Distribute total working hours to three shifts

    From an email sent to me:

    Quote 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...

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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