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

[Chi Santo]

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

[GeoffW283]

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:

=IF(ISNUMBER(B$1),

MIN(SUMIFS('Example hours'!$F:$F, 'Example hours'!$B:$B, ">"&B$1-14, 'Example hours'!$B:$B, "<="&B$1-7, 'Example hours'!$A:$A, $A3), 40)/24 +

MIN(SUMIFS('Example hours'!$F:$F, 'Example hours'!$B:$B, ">"&B$1-  7, 'Example hours'!$B:$B, "<="&B$1,    'Example hours'!$A:$A, $A3), 40)/24,

MAX(SUMIFS('Example hours'!$F:$F, 'Example hours'!$B:$B, ">"&A$1-14, 'Example hours'!$B:$B, "<="&A$1-7, 'Example hours'!$A:$A, $A3) - 40, 0)/24 +

MAX(SUMIFS('Example hours'!$F:$F, 'Example hours'!$B:$B, ">"&A$1-7, 'Example hours'!$B:$B, "<="&A$1, 'Example hours'!$A:$A, $A3) - 40, 0)/24)

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

[Chi Santo]

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

[GeoffW283]

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

[Chi Santo]

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.

[Chi Santo]

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.

[JeteMc]

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:

=IF(ISNUMBER(D$1),

MIN(SUMIFS('Example hours'!$F:$F, 'Example hours'!$B:$B, ">"&D$1-14, 'Example hours'!$B:$B, "<="&D$1-7, 'Example hours'!$A:$A, $A3), 40) +

MIN(SUMIFS('Example hours'!$F:$F, 'Example hours'!$B:$B, ">"&D$1-  7, 'Example hours'!$B:$B, "<="&D$1,    'Example hours'!$A:$A, $A3), 40),

MAX(SUMIFS('Example hours'!$F:$F, 'Example hours'!$B:$B, ">"&C$1-14, 'Example hours'!$B:$B, "<="&C$1-7, 'Example hours'!$A:$A, $A3) - 40, 0) +

MAX(SUMIFS('Example hours'!$F:$F, 'Example hours'!$B:$B, ">"&C$1-7, 'Example hours'!$B:$B, "<="&C$1, 'Example hours'!$A:$A, $A3) - 40, 0))

You'll see that the values in D3:D7 are now displayed as in S3:S7.
Let us know if you have any questions.

[GeoffW283]

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:

=IF(C2="", 0, B2+C2-MOD(Weekly!$B$1, 1))

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:

=MAX(D2+E2-MOD(Weekly!$B$1, 1), 0) +N(M1)/24

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:

=I2-H2

This formula is just time worked expressed as a fraction of 1 day (24 hours)
In K2 and copied down:

=J2*24

This formula is just time worked expressed in hours
In L2 and copied down:

=IF(INT(H2)=INT(I2), K2, 24*(1-MOD(H2, 1)))

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:

=K2-L2

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.

[Chi Santo]

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.

[JeteMc]

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