Trying to see when Leave and OT falls on the same week
Our take
Hello,
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.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Attendance Occurrence Calculation in excelHi all! I run attendance reports on my associates and want to calculate a total number of occurrences over a given time and total number of hours missed. I can of course do simple COUNTIF or adding up hours, but the consecutive is confusing me. Items to note- - The report generates the data for all employees at once, can be up to 20 or so, depending on team. - The layout shows name and employee number, absence coding (unscheduled and unpaid unscheduled), date, amount of time missed - it would need to count consecutive days as 1 occurrence (excluding weekends) *bonus points if it can differentiate between consecutive full day absences or partial day absences. Consecutive partial day absences would count as individual occurrences, not a single one. A full workday is 8 hours. Thank you! EDIT- add example data from report 8/20 and 8/21 are consecutive full day absences, so would count as 1 12/19 and 12/22 are consecutive because we are excluding weekends, but NOT full day absences so would count as individual occurrences. This person would then have 4 total occurrences. REP NAME CODING DATE HOURS NAME Unpaid Absence Unscheduled 8/20/2025 8 NAME Unpaid Absence Unscheduled 8/21/2025 8 NAME Unpaid Absence Unscheduled 11/26/2025 8 NAME Unpaid Absence Unscheduled 12/19/2025 2.5 NAME PTO Unscheduled 12/22/2025 2 submitted by /u/DryFault4 [link] [comments]
- Issues with scaling a work roster scheduleUsing a leave register I am trying to automate an AM/PM work roster. The Leave Register counts blank cells to confirm who's available, & I'm filtering a randomised list of available staff in a separate column (N:Q in image 2): =LET( P1AM, FILTER('Leave Register 2026'!A92:A98, 'Leave Register 2026'!C92:C98 = ""), SORTBY(P1AM, RANDARRAY(ROWS(P1AM))) ) https://preview.redd.it/y16be41qopqg1.png?width=1008&format=png&auto=webp&s=6ecbcd87549c4537f331b71a0b38150d0ff1d409 I'm using variations of the formula below in C11:C28 & D11:D28: =LET( P1AM, N4#, UsedNames, VSTACK(range to check to avoid duplication), AvailableNames, FILTER(P1AM, ISNA(XMATCH(P1AM, UsedNames)), ""), IF(ROWS(AvailableNames)=0, "", INDEX(AvailableNames, 1)) ) The Sales cells (Rows 4 & 7) are manually entered by the user at the start of the week, so each cell below this checks these before populating a name, along with any cells populated prior to the specific cell in question. As each cell has a different range it's checking, this seems hard to grow without manual entry across a week let alone at an annual scale. https://preview.redd.it/h5dqw6uqopqg1.png?width=1248&format=png&auto=webp&s=38ca2bdc5e3e809779a4391437fdc8227b3ee538 submitted by /u/FishingElbow [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]
- 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]