+ Reply to Thread
Results 1 to 6 of 6

Need to calculate hours worked daily and have a total for each week (Bi-weekly pay)

  1. #1
    Registered User
    Join Date
    01-09-2016
    Location
    Geneva, IL
    MS-Off Ver
    MS Office 365
    Posts
    3

    Question Need to calculate hours worked daily and have a total for each week (Bi-weekly pay)

    I'm trying to create a spreadsheet where the first sheet show's dates, daily totaled hours, and will show Totaled hours for each week, and a section for bi-weekly totaled hours with gross pay as well. I'd like to have the formulas on a second sheet with the actual start time, break time (subtracted),& end time added in with hourly wage to go into the gross pay box. Please help.

  2. #2
    Registered User
    Join Date
    05-10-2015
    Location
    Ryton
    MS-Off Ver
    Office 2013
    Posts
    49

    Re: Need to calculate hours worked daily and have a total for each week (Bi-weekly pay)

    Could you post a worksheet which shows the layout of how you want it to look

  3. #3
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: Need to calculate hours worked daily and have a total for each week (Bi-weekly pay)

    There are so many forms this could take and other variables that you may not have mentioned as well.
    A sample sheet of how you would like to see the data would make life a lot easier

    There are heaps and heaps of free templates out there and also heaps of threads in this forum with timesheet and wage questions and examples.

    But using your description, I came up with something that might be along the lines of what you want.
    Hopefully it will give you some ideas if you are looking for some and show you some formulas that you could adjust to suit your own layout.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-09-2016
    Location
    Geneva, IL
    MS-Off Ver
    MS Office 365
    Posts
    3

    Re: Need to calculate hours worked daily and have a total for each week (Bi-weekly pay)

    Thank you so much Beamer!! This is exactly what I was looking for! Is there a way you might be able to tell me the formulas you used?

  5. #5
    Registered User
    Join Date
    01-09-2016
    Location
    Geneva, IL
    MS-Off Ver
    MS Office 365
    Posts
    3

    Re: Need to calculate hours worked daily and have a total for each week (Bi-weekly pay)

    That spreadsheet is perfect! Exactly what I needed!! Can you share the formulas you used?

  6. #6
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: Need to calculate hours worked daily and have a total for each week (Bi-weekly pay)

    I'm glad it worked for you. Sorry I took so long to get back to you, I've been away.

    On the timesheet page, the formula in column H is a simple addition of time (startbreak-startwork + finishwork-finishbreak).
    Column I (Std Hours) simply provides the minimum (or lowest) amount between the hours worked or 8 (8 being a full day at standard hours).
    Column J (Overtime) provides and leftover amount of hours over 8. Maximum of either totalhours-stdhours or 0.
    Actually, since stdhours cannot be less then totalhours....the formula in J can simply be =H3-I3.

    On the Wages Tab, Cell C9:
    The SUMPRODUCT function sums up all of the rows that meet the criteria in each of it's following arrays.
    The First (Timesheet!$B$3:$B$1000=B9) tells it to only look for names that match B9 (Beamer).
    The second (Timesheet!$C$3:$C$1000>=$C$2) tells it to only use dates that are equal to or after the week1 start date (C2).
    The Third (Timesheet!$C$3:$C$1000<=$C$3) tells it to only use dates that are equal to or before the week1 end date (C3).
    And the fourth (Timesheet!$I$3:$I$1000) has no criteria to match so it adds them all.
    So to review....It looks at all of names from B3:B1000 and picks out Beamer, then it narrows that list down to only dates including and after the start date, then it rules out dates after the end date, and adds all the times together for the rows that are left.
    Other than simple additional, all the other formulas on that sheet work the same way.

    Oh, the Gross Pay..pretty straight forward....it multiplies the overtime hours by the O/T multiplier, then adds the standard hours and multiplies the lot by the hourly rate.
    So in my example, 5.5hrs O/T x 1.5 = 8.25 + 79 std hours = 87.25 x $15.25 /hr = $1330.56

+ 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. (Help) Time Sheet total hours worked per day and overall week
    By SirDraco in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 11-10-2014, 05:35 AM
  2. Total hours worked for the week
    By bud11dy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-15-2013, 09:18 AM
  3. Calculate Worked hours per week from a client dropdown list
    By Garibaldi in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-21-2013, 04:30 AM
  4. [SOLVED] Breakdown of total daily worked hours
    By beitzy in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 02-05-2013, 10:11 PM
  5. [SOLVED] Creating a population of total hours worked for that week ?
    By calamityjane in forum Excel General
    Replies: 8
    Last Post: 09-13-2012, 06:00 PM
  6. Replies: 0
    Last Post: 04-25-2012, 05:42 PM
  7. [SOLVED] Getting a total of hours &amp; mins worked per week
    By big_weegie in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 06-16-2005, 07:05 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