PTO available Balance

[Robert Wyatt]

I have a f

PTO Register.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Employees Date of Hire	Years of Service Years Months Days			Annual Accrual Days	Maximun Accrual	Accrual Per Hours	Accrual Per Pay Period	Avaiable PTO Hours				Years of Service	Annual Accrual Days	Maximum Accrual	Accrual Per Hours	Accrual Per Pay Period
2	03/22/2024	0	0	10	10	80	0.038461538	1.54	-				0	10	80	0.038461538	1.54
3													1	12	96	0.046153846	1.85
4				Balance Forward									2	15	120	0.057692308	2.31
5				Accrualed Hours		-							5	20	160	0.076923077	3.08
6				PTO		-							10	25	200	0.096153846	3.85
7				Sick		-
8				Vacation		-
9				Holiday		-
10
11	Pay Period #	Pay Date	Hours	PTO	Sick	Vacation	Holiday	Accrual Rate	Balance
12	26	12/31/2023	Balance Forward						-
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
Sheet1

Cell Formulas
Range	Formula
C2	C2	=IF(B2="","",DATEDIF(B2,TODAY(),("Y")))
D2	D2	=IF(B2="","",DATEDIF(B2,TODAY(),("YM")))
E2	E2	=IF(B2="","",DATEDIF(B2,TODAY(),("MD")))
F2	F2	=LOOKUP(C2,N2:O6)
G2	G2	=LOOKUP(C2,N2:N6,P2:P6)
H2	H2	=LOOKUP(C2,N2:O6/260)
I2	I2	=LOOKUP(C2,N2:O6,R2:R6)
J2	J2	=VLOOKUP(J12+100,J12:J64,1)
Q2:Q6	Q2	=O2/260
R2:R6	R2	=P2/52
G5	G5	=SUM(H13:H38)
G6	G6	=SUM(E12:E38)
G7	G7	=SUM(F12:F38)
G8	G8	=SUM(G12:G38)
G9	G9	=SUM(H13:H38)
J12	J12	=SUM(G4)
I13	I13	=IF(D13="","",LOOKUP($C$2,$N$2:O6/260*D13))
J13	J13	=IF(D13="","",IF(AND(ISBLANK(B13:H13)),"-",IF(J12+I13-D13-E13-F13-G13-H13<$G$2,J12+I13-D13-E13-F13-G13-H13,)))

ormula in the PTO balance Colum J13 it was working, but for some unknow reason it quit. Could some one look at it and see where I missed up. I for got to add 40 hrs so it would show to #value error.

 

[Robert Wyatt]

There are two things you can do to help debug your formulas (i am unsure if you did this or not).
One is to use the formula evaluate tool: Select Formulas from the menu, then in the formula auditing section click on Evaluate Formula. You can click through the dialog box to see how each expression in your formula is evaluated.
The second is to use the F9 function key inside the formula. Highlight an expression (say: A2+B2) and click F9, it will give you the calculation for that expression. (But you need to be careful with this and click ESC key or the newly calculated value will get inserted into your formula).
In some version, just hovering over the highlighted expression will give you the calculation.

Yes, I used the formula evaluate tool and I found where my mistake was. and for the second one I had no idea about it. But I do have the program up and running now and it works great. thanks the subjections. I have already marked as solitons from mark telling me how to fix the problem. Thanks again.