+ Reply to Thread
Results 1 to 9 of 9

Timesheet Calculation for Shift workers

  1. #1
    Registered User
    Join Date
    08-30-2013
    Location
    Melbourne
    MS-Off Ver
    Excel 2010
    Posts
    6

    Timesheet Calculation for Shift workers

    Hoping to get some more help here today with my timesheet as I did yesterday.

    As per attached spread sheet in the yellow highlighted cells, I am trying to input a formula to automatically calculate the hours between specific times for shift workers so I can easily calculate their penalty rates however I just cant seem to get it to work, the main issue being midnight.

    Any help would be greatly appreciated.

    Cheers
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Timesheet Calculation for Shift workers

    since normal hours end at 22:00. test if the worker finished after 10, then in normal hours subtract 22-(start time)*24, after midninght hours should be end time *24 if the exit time < than start time. use the total hours subtracted from normal hours and after midnight hours to calculate the late hours.
    Last edited by rcm; 06-23-2014 at 11:00 PM. Reason: typo

  3. #3
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Timesheet Calculation for Shift workers

    It's not very refined or elegant, and there's probably a better way to do it, but this works:Penalty Hours Worked Test.xlsx
    Please add reputation by clicking on the * if I have helped.
    Please mark the thread SOLVED if your issue has been resolved.
    Thanks, Glenn.

  4. #4
    Registered User
    Join Date
    08-30-2013
    Location
    Melbourne
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Timesheet Calculation for Shift workers

    Thanks so much for your replies rcm & gak67. Very helpful and I am definitely getting somewhere now. Both calculations work with that one particular scenario but if I have different start/ finish times, then I cant get it to work quite right.

    I have attached another spread sheet with your suggested calculation RCM, it did work perfectly until I put in an end time that is less than 22:00 then it throws it out (Note: I have highlighted the incorrect calcs in red). I tried adjusting it, then that scenario worked but another didn't. Any suggestions? I think I have now been looking at it too long now that it is probably something simple that I just cant put my finger on!

    Any further suggestions would be greatly appreciated.

    Looking forward to hearing back
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    06-23-2014
    Location
    Dubbo, Australia
    MS-Off Ver
    EXCEL 2019
    Posts
    180

    Re: Timesheet Calculation for Shift workers

    A simple change to cells in D fixed the normal hours rule, and conditional formatting of the penalty columns to hide 0 or <0 values cleans the sheet up
    Attached Files Attached Files

  6. #6
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Timesheet Calculation for Shift workers

    Find the attached file
    Attached Files Attached Files
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  7. #7
    Registered User
    Join Date
    08-30-2013
    Location
    Melbourne
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Timesheet Calculation for Shift workers

    Thanks for your reply dubcap01, however I dont think I was clear with what normal hours represent. Normal Hours is time worked between 7am to 10pm. Normal meaning no penalty rates. Total Hours is the employees total hours for that shift.

    Therefore, for example, cell D2 should equal 5. 5 hours between 5pm to 10pm. Cell E2 should equal 2 (which is correct), and cell F2 should equal 1.5 (also correct). So in this instance, its the normal hours which I am having trouble with.

    The three time frame examples all need to work as they are all examples of hours that an employee may work in any given week.

    Hoping you may have another suggestion?
    Thanks

  8. #8
    Registered User
    Join Date
    08-30-2013
    Location
    Melbourne
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Timesheet Calculation for Shift workers

    Thanks nflsales! That works perfectly. I didnt realise when I first looked at it as the hours calcs were in time format so I just multiplied all by 24 which worked.

    Thanks again, I really appreciated your help

  9. #9
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Timesheet Calculation for Shift workers

    TRy it now
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    08-30-2013
    Location
    Melbourne
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Timesheet Calculation for Shift workers

    Fantastic! Thanks again rcm

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Timesheet calculations with overtime and shift differential
    By JHSVic in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-17-2014, 10:57 AM
  2. Timesheet calculation
    By sinternational in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-03-2013, 09:34 AM
  3. Day shift/night shift time calculation issue
    By STATEXCEL in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-31-2007, 08:48 AM
  4. Timesheet Calculation
    By btoti in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-18-2006, 03:30 PM

Tags for this Thread

Bookmarks

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