•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How to calculate hours worked per pay period per employee?
Our take
Tracking hours worked per pay period can be daunting, especially when managing overtime for multiple employees. In this guide, we’ll explore how to effectively calculate and summarize hours worked, ensuring your HR team has a clear view of employee contributions. With a well-structured spreadsheet that highlights key data points, you can streamline the process and eliminate confusion. We'll also address common pitfalls, like unwanted values in your calculations, so you can create an accurate and user-friendly summary page. Let’s dive in!
I am helping with making a spreadsheet to help HR keep track of the OT hours each employee works each pay period.
My spreadsheet has the following relevant columns:
- B: Name
- H: Date
- I: Week #
- J: pay period (calculated biweekly)
- K: time start
- L: time end
- M: total hours
I am trying to create a summary page that will list the employees and then the pay periods (1, 2, 3.....) and just show how many hours they worked each pay period
I tried doing a pivot table, but it did not look how I wanted
Also, for column J, how can i make it so if there is not a value on column H it does not show -3286
here is the formula i used for the pay period
=INT((H6-DATE(2025,12,14))/14)+1
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Trying to see when Leave and OT falls on the same weekHello, We are making some updates at work to our pay system based on new rules for employees end of year reporting of overtime. With the new rule if an employee has paid leave and OT occurring in the same work week, those OT hours are not eligible for the deduction. For example, if you work 5/8s M-F and were out on sick leave for a dental appointment for 2 hours Monday, and then worked OT for 8 hours Saturday, then 2 hours of that OT would be coded as 1232 for OT, and 6 hours as 1432 for the new provision. For our employer paid leave is considered working time and does count towards OT. The new updates should be in our system soon. But once it is in, we will have to change the code for retro OT actions that occurred from 12/16/2025-present. What I'm trying to do is use the data from one of the reports that is converted to a spreadsheet, and try to pick out the folks who have paid leave in the same week that they have OT. We employ approx 3000 people, and about 200-300 are approved for OT each pay period. A lot more people use paid leave in a pay period. Below is a example of how the data is produced from the report (this is fake data that I wrote up real quick at home). Personnel and Name would be the same person. The system tracks absence with a H unit of measurement, and Excess with a HR. The column is not necessary, and could be deleted as well. But also thought it might be helpful to simplify an IF formula. Absence = paid leave. There are tons of paid leave codes for things like Sick Leave, Sick Leave Care of Family, Sick Leave Emergency Self, Sick Leave Emergency Family member, etc. OT just has 1 wage type, 1232, which make things easier. What I'm trying to do is be able to quickly identify if someone has OT and paid leave in the same work week. For example, John and Bryan have leave only, so those two I could skip. Jane has OT only. Mark has OT and Leave, but they do not occur in the same work week. So Jane and Mark can both be changed to 1432 later on. Brian (got that South Park song in my head while making up fake names) has OT and Paid leave in the same work week. So that would be one that would need to be flagged. That way we can double check it further and make sure that record reflects 2 hours 1232, and 6 hours 1432 in our system. When I ran the data for 3/1-3/15 pay period, it resulted in 8000 lines in the Excel report. So being able to quickly identify like highlighting those I need to look at, or somehow change the Group function so that it only expands people with the 1232 code and collapses everyone else would both be ideal. I've been trying to play with Pivot Tables, but can't get it to display what it is I'm trying to focus on. If anyone has any helpful Excel tips it would be very much appreciated. Thank you. https://preview.redd.it/p0n5zjql3gqg1.png?width=901&format=png&auto=webp&s=477f03264477ad6f8af31ca64a9fe47bfded7a6e submitted by /u/Bored_NightOwl_314 [link] [comments]
- Formula for calculating regular and overtime hours based on a full week with an 8 hour day.My excel sheet has multiple employees, dates and the total number of hours worked for that day per employee. I currently have a formula that calculates 40 for the week as regular time then the remainder is overtime but that's not how our overtime works. I need to create a formula(s) to calculate hours per day up to 8 as regular time then the remainder is overtime including all day Saturday and Sunday I know this should not be as difficult as my mind has made it but for the life of me, I can not figure it out, plus I'm old so using AI is out of my scope. For example, Christian only had 26 hours for the week so my current formula does not figure any overtime when he should actually have 16 regular hours and 10 overtime hours. Also for example, Adan should have 24 regular hours and 39 overtime hours. https://preview.redd.it/i0ahs8oaf6yg1.png?width=429&format=png&auto=webp&s=e0e29134a1630561de2bc997571fab197ceabf43 Please help. Thank you! submitted by /u/Ok-Fail-4340 [link] [comments]
- Monthly tracking workbook I use to track employee sales metrics; Trying to find a way to make the process less labour intensiveTruly having a hard to describing my issue effectively but hoping someone can help. First time posting here and I'm by no means an expert with excel, so please be kind! I have a monthly workbook where I track each employees revenue and other metrics. Every 2 weeks for payroll, I provide a print out of these numbers, and the payroll sheet pulls data from multiple sheets in the workbook. For example, every workbook has a separate sheet for each day of the month, titled "1" through "31". I have pay period sheets, so I'll use one titled "04.02.26 - 04.15.26". Then I'll have the data for each employee pulled from multiple sheets. For example, I use the formula =SUM('2:15'!E3) to pull the sales data from each day of that period for the specific employee. This works quite well. However, when I create a new month's spreadsheet, I have to manually alter this formula for each employee and for each data point (more than just revenue, at least 6 different data points for 7 employees). Is there a way to automate this? For example, a cell or two where I'm able to enter the date range and all of the formulas update to that date range for the corresponding pages? I'm sorry of this post is confusing. Truly it's confusing even typing it! submitted by /u/Ok_Smile9222 [link] [comments]
- Time In Lieu SpreadsheetHey All, I have used Google Sheets forever but my new job uses Office and I am struggling with some of Excel's functions. I'm trying to create a spreadsheet that tracks my time off in lieu (TOIL) so I know where I'm at. I've created the bare bones of the spreadsheet ash shown in the image but I need help cleaning it up. Ideally I would like to have all of the time columns (B:H) only have hh:mm format and then all of the 0:00:00 values that haven't been used not to be shown. In cell L1 I would like it to show the most current level of TOIL, again in hh:mm format. Is this possible or am I asking too much of Excel? Thanks in advance. https://preview.redd.it/c8u5ha2cxszg1.png?width=1001&format=png&auto=webp&s=3985daa3d11541abed488f377c62e375f6ab1224 submitted by /u/Silky_45 [link] [comments]
Tagged with
#Excel alternatives for data analysis#natural language processing for spreadsheets#modern spreadsheet innovations#machine learning in spreadsheet applications#generative AI for data analysis#enterprise-level spreadsheet solutions#digital transformation in spreadsheet software#collaborative spreadsheet tools#AI-driven spreadsheet solutions#cloud-based spreadsheet applications#real-time data collaboration#automation in spreadsheet workflows#no-code spreadsheet solutions#rows.com#AI formula generation techniques#Excel compatibility#financial modeling with spreadsheets#hours worked#pay period#OT hours