+ Reply to Thread
Results 1 to 10 of 10

Bi-Weekly, Multiple Employees, Hourly & Overtime Calculations Wanted

  1. #1
    Registered User
    Join Date
    09-30-2020
    Location
    Shreveport, LA (USA)
    MS-Off Ver
    MS 365
    Posts
    6

    Bi-Weekly, Multiple Employees, Hourly & Overtime Calculations Wanted

    Hello All,

    Thank you for taking the time out of your day to look at my problem and provide aid or at the very least encouragement for its resolution.

    The excel file uploaded to this post is an example of what the Clover POS/Dashboard System pumps out for us to process payroll.

    With this info I want to copy and paste onto a spreadsheet where the formulas will pull from this data file and produce the hours worked for each employee.

    My trouble is determining the composition of the formula to seek (vlookupmaybe?) the employee name, and then determine if they worked <40+ hours in Week One and Week Two. If more than 40 in either week, in a separate cell, show me the Overtime Hours.

    Apologies for my inexperience with asking for help as well as mishaps with my file loading/not loading.

    Looking forward to learning from everyone so I may one day help others.

    Thank you,

    Santino Giacalone
    Chianti Restaurant
    Shreveport, LA
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Bi-Weekly, Multiple Employees, Hourly & Overtime Calculations Wanted

    Welcome to the forum

    I have created a worksheet called "BiWeekly" with a guess at what you might be looking for. If this is something close to what you are looking for then great. If not then you should add a worksheet of your own with the layout that you are looking for and add a few manually calculated expected results and then re-post the workbook. That will maximize your chances of a quick and accurate response.

    Example_hours_gw.png

    A few notes on my guess:

    I assumed that clock-in/out times never span midnight

    The formula in B3 copied across and down to I5 is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    It's not as bad as it looks - it's 4 repetitions of a formula with minor differences to accommodate the need to report on a biweekly basis but calculate overtime on a weekly basis.

    I formatted B3:I10 as [h]:mm as we're displaying durations rather than time.

    I added "Emp C" and provided some "Elapsed hours" data in order to check overtime calculations (none of your data strayed into overtime).

    As noted above, if this is not close to what you are looking for then please add a worksheet of your own with the layout that you are looking for and add a few manually calculated expected results and then re-post the workbook.

    Hope this helps
    Attached Files Attached Files
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select ?Solved? from the Thread Tools menu

  3. #3
    Registered User
    Join Date
    09-30-2020
    Location
    Shreveport, LA (USA)
    MS-Off Ver
    MS 365
    Posts
    6

    Re: Bi-Weekly, Multiple Employees, Hourly & Overtime Calculations Wanted

    My heart is fluttering with joy from this perfectly created formula. This is precisely what I was looking for and I cannot thank you enough.

    Expanding on your assumption on midnight clock outs, how would the formula need to be tweaked to accommodate this situation? We have a handful of employees who work beyond midnight during the week which I could manually adjust as needed.

    Thank you so much again.


    Edit: How must the formula be tweaked so as to consider the work week beginning on Mondays 7am until Mondays 6am?

    Sorry and thank you
    Last edited by Chi Santo; 10-02-2020 at 12:54 PM. Reason: Start of 40 hour Work Week Consideration

  4. #4
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Bi-Weekly, Multiple Employees, Hourly & Overtime Calculations Wanted

    Well, somewhat less than perfectly created with regard to midnight crossings - but thanks for the feedback and reputation point on the first attempt!

    Starting days at 7:00 am won't be just a "tweak" to the formula - for simplicity I had only used "Elapsed hours". That will need to change to use the clock in/out times instead.

    Before I (or someone else) starts to tackle this, a couple of questions just to make sure I have the requirements correct:

    You said: "Consider the work week beginning on Mondays 7am until Mondays 6am"
    So what happens to time worked between 6am and 7am? I think we need just a single divider date. Can I assume Monday 7am?

    Just to clarify, can you confirm this test case?
    Clock in: Week-1 Sunday 9:00 pm
    Clock out Week-2 Monday 8:00 am
    Results in:
    --> 10 hours allocated to week-1
    --> 1 hour allocated to week-2

    Thanks

  5. #5
    Registered User
    Join Date
    09-30-2020
    Location
    Shreveport, LA (USA)
    MS-Off Ver
    MS 365
    Posts
    6

    Re: Bi-Weekly, Multiple Employees, Hourly & Overtime Calculations Wanted

    It's like you are reading my mind; my experience with excel is still quite crude yet I am able to mimic others incredible work. So again, much thanks for being patient with me and providing concise, exceptionally helpful aide.

    Your observation with my 7am to 7am on Mondays is accurate; It should consider the hour in between. No one here will ever be clocking out beyond 5am each night/morning; nor will they ever be clocking in before 7am each morning. However, providing room for that contingency is probably a good exercise in pragmatism when thinking on Murphy's Law.

    Your test case is correct and is the crux of my issue when processing payroll.

    I cannot add any more reputation at this point but from my perspective it is well deserved. Thank you for welcoming me to this community with such generosity. Looking forward to learning so I may better help others myself.

  6. #6
    Registered User
    Join Date
    09-30-2020
    Location
    Shreveport, LA (USA)
    MS-Off Ver
    MS 365
    Posts
    6

    Re: Bi-Weekly, Multiple Employees, Hourly & Overtime Calculations Wanted

    Hello GeoffW283,

    I wanted to share with you the results of your formula versus my "manual" workings each payroll.

    You can see that your formula is working outstandingly!

    In reference to your last clarification, need I adjust the formula to a "Single Divider Date" or based on the info I've provided, is it still logical to allow your formula to perform as is?

    Another question I had is in relation to the "result display"; as in, I'm taking the Hours and Overtime hours results and entering them into Quickbooks which converts my 28.50 (number), for example, into 28:30 (time) to be paid. Your formula is showing a "time" result versus "numerical?" (not quite sure to be honest).

    Please forgive me if I am coming across as a stickler, I am just making sure that these formulas are being fair to the employees as well as the business.

    Thank you so much for your help with my questions.
    Attached Files Attached Files
    Last edited by Chi Santo; 10-13-2020 at 03:58 PM. Reason: Add Excel Attchment/fixing english

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Bi-Weekly, Multiple Employees, Hourly & Overtime Calculations Wanted

    As to whether or not the displayed values in D3:D7 are times or numeric values they are times. One way to discern is to select cell D3 and see that if the format were set to general it would be a fraction.
    If you would rather have numerical output then remove the /24's from the formula so that it reads:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    You'll see that the values in D3:D7 are now displayed as in S3:S7.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  8. #8
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Bi-Weekly, Multiple Employees, Hourly & Overtime Calculations Wanted

    First, apologies for the delayed response. I hope this is still useful.

    If your primary goal is to import to Quickbooks, then there is probably a better layout than than the "Weekly" worksheet that I have provided?

    Again, as you intend to import to Quickbooks then I'm guessing that you want the results in terms of hours and fractions and so, per JetMC's comment I have removed the "/24"s from the formulas in the attached workbook.

    The two remaining issues were to deal with midnight crossings and to recognise that days begin at 7:00 am and that time worked between midnight and 7:00 am belongs to the previosu day. That (as best as I can come up with) adds quite a bit of complexity - Maybe someone else has a much cleaner approach!

    Here's some details relative to the attached workbook:

    To implement the 7:00 am day start:
    In Weekly!B1 (the blue shaded cell) enter the the END date AND time of the two week period. For example: 27-Sep 7:00 am. The 7:00 am portion of the date/time is your day begin time.

    A number of "helper" columns are needed on the "hours" worksheet to the right of your imported data (the helper columns can be located elsewhere if necessary).
    In H2 and copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This formula moves the clock in date/time back by 7 hours so that, for example, time worked between midnight and 7:00 am is counted towards the previous day.
    In I2 and copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This formula is the same 7 hour adjustment as for the "In" date/time plus "carry over" hours from the previous day. The max() is just to avoid negative times (which Excel doesn't deal with) for empty rows.
    In J2 and copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This formula is just time worked expressed as a fraction of 1 day (24 hours)
    In K2 and copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This formula is just time worked expressed in hours
    In L2 and copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This formula determines if the clock out date is greater than the clock in date. If so then some or all of the time belongs to tomorrow, reducing the "Today Hours" time in this column.
    In M2 and copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This formula is the hours to be carried over to tomorrow (in col-I)

    The formulas on the "Weekly" worksheet are the same as before except that they point to the adjusted hours in col-L rather than directly to "Elapsed Hours" in col-F. Also - important - cell B1 needs to be a date/time with the time part = 7:00 am.

    Hopefully there is something you can work with here. Let us know.

    The attached workbook implements the above.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    09-30-2020
    Location
    Shreveport, LA (USA)
    MS-Off Ver
    MS 365
    Posts
    6

    Re: Bi-Weekly, Multiple Employees, Hourly & Overtime Calculations Wanted

    You guys are the best; one of my family members has been told of this website due to the great help I've received from this one issue of mine.

    I'll know in the future to be more careful with my verbiage when referencing problems since I am not digitally importing excel to quickbooks but rather manually. I print out my work to manually input into quickbooks since there is other information in addition to hours needed to go into quickbooks.

    I cannot thank you guys enough and look forward to coming up with more puzzles for you guys to wrap your heads around haha.

    Thanks again.

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Bi-Weekly, Multiple Employees, Hourly & Overtime Calculations Wanted

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. [SOLVED] Count employees working hourly
    By sprit36 in forum Excel Programming / VBA / Macros
    Replies: 29
    Last Post: 07-21-2017, 02:34 AM
  2. Calculating daily overtime and weekly overtime
    By Guy Montague in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-22-2016, 04:55 PM
  3. Replies: 1
    Last Post: 02-29-2016, 06:11 PM
  4. i wanted to allocate data for number of employees using exce-vba and database
    By baig123 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-10-2014, 02:43 AM
  5. Replies: 1
    Last Post: 02-28-2014, 02:31 AM
  6. hourly rate and overtime factor?!
    By maci3j in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-15-2008, 12:33 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