I have an accrual formula post out there if you have time or want to take a look at it. 
Posts by KEW115
-
-
Worked perfectly. Thank you so much.
-
-
There are two tabs that are masters/summary sheets. I did not color code them. I would like them to appear at the beginning of the workbook. So I guess that would mean not to include them in the sort. Color sort order I would prefer:
1st color RGB Red: 112 Green: 48 Blue: 160
2nd color RGB Blue: 254
3rd color RGB Red: 225
Currently there are 246 tabs. Since our turnover rate is rather high, I am sure the tabs will increase throughout the year.
I truly appreciate your time and help. Thank you so much
-
Code
Display MoreHi Jeff, I completely understand. What is obvious to me may not be obvious to someone reading my post. Thank you for your input. Yes, each tab represents an employee. Option Explicit Sub test() Dim ws As Worksheet, myColor, x, i As Long, ii As Long Dim myList, w(), n As Long myColor = VBA.Array(208) Application.ScreenUpdating = False ReDim myList(1 To UBound(myColor) + 1) For Each ws In Sheets x = Application.Match(ws.Tab.Color, myColor, 0) If IsError(x) Then ReDim Preserve myColor(UBound(myColor) + 1) ReDim Preserve myList(1 To UBound(myColor) + 1) myColor(UBound(myColor)) = ws.Tab.Color x = UBound(myColor) + 1 End If If Not IsArray(myList(x)) Then ReDim w(1 To 2, 1 To 1) Else w = myList(x) ReDim Preserve w(1 To 2, 1 To UBound(w, 2) + 1) End If w(1, UBound(w, 2)) = IIf(IsNumeric(ws.Name), Val(ws.Name), ws.Name) w(2, UBound(w, 2)) = ws.Name myList(x) = w Next For i = 1 To UBound(myList) If IsArray(myList(i)) Then mySort myList(i), 1 End If Next For i = 1 To UBound(myList) If IsArray(myList(i)) Then For ii = 1 To UBound(myList(i), 2) Sheets(myList(i)(2, ii)).Move after:=Sheets(Sheets.Count) Next End If Next End Sub Private Sub mySort(a, ref) Dim i As Long, ii As Long, iii As Long, temp For i = LBound(a, 2) To UBound(a, 2) - 1 For ii = i + 1 To UBound(a, 2) If a(ref, i) > a(ref, ii) Then For iii = LBound(a, 1) To UBound(a, 1) temp = a(iii, i) a(iii, i) = a(iii, ii) a(iii, ii) = temp Next End If Next Next End Sub -
Hi Jeff, The tab colors are as such: active employees: blue - termed employees: red - other: light green. I wanted to have the workbook sort alphabetically within the color group, but I wanted to see if I could pick the colors in the order in which they show in the workbook. This is the order I wanted them to appear in the workbook; active, other and terms. I ended up changing the tab colors so that so I get this order. I'm sure there is another way, but this is working for me. I am very new to working with VBA. Hopefully, I don't need to add another group color.
Thank you for responding to my post. -
Oh! One more thing. We had a accrual rate change on 11/1/20 for certain departments.
-
Hi! Years of service. I so appreciate you looking at this.
-
Hope this helps. I need the accrued column to calculate what is accrued for the pay period based on the maximum allowed and not what what was accrued for the pay period based on the hours worked * accrual rate.
Example: row 71 column F should display 4.9744 and not 6.1600
-
Ok - thanks for helping. This employee's max is 120. I want the accrued column to calculate the hours she can earn without going over the max. Pay date 1/28 the PTO balance is 115.0256. The amount she can earn is 4.9744. The accrual rate column is showing 6.16 because she worked 80 hours and the accrual rate is .065400
-
Thank you! This works and returns 140. However, I would rather find a formula in the accrued hours column....if possible. Example. The accrued hours columns shows what the employee accrued for the pay period. If the previous pay period had a PTO balance of 135.49 then employee could not accrue the full 8 hours of PTO. They could only accrue 4.505. I would like the accrued hours column to show the 4.505. I hope I am explaining that correctly.
These are my formulas
accrued hours column =E72*$M$4 E72 is the total hours $m$4 is the accrual rate
PTO balance column no max =H71+F72-G72
PTO balance column with max =IF(H72<=140, H72, IF($H72>=140, "140.00"))
I probably made this more complicated than what it needed to be. Trying to simplify
-
Hello!
I am trying to figure out a formula that will calculate accrued PTO hours based off of total hours worked plus PTO taken for the pay period. There is a catch to this formula. I have the first part figured out. I am stuck when it comes to maximum earned. Example: Employee's PTO balance is 135.4950. The total maximum allowed for this employee is 140.00. They worked 80.00 for pay period and have an accrual rate of 0.10000. If you take 80 times the accrual rate of 0.10000, that equals 8 hours. However, they can only accrue 4.505 because they are almost at their max of 140.00. I hope I explained this clearly. Any help is much appreciated.
-
Hello! I saw your post regarding sorting tabs by color and alphabetically. It helped tremendously. Thank you. However, I am having trouble figuring out how to specify the color order. I track our employee's PTO in excel and color code then by active, term and if they change status to no longer eligible for PTO. I played around with changing the tab colors, but my terms are still showing up at the beginning of the workbook. Any help is much appreciated.