Trying to see when Leave and OT falls on the same week
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]
Want to read more?
Check out the full article on the original site