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.

 

[awoohaw]

you may want to send a message to a moderator/admin (report yourself) asking to delete the erroneous posting. But, you also are not asking a question here. what do you want to have done?

 

[Robert Wyatt]

you may want to send a message to a moderator/admin (report yourself) asking to delete the erroneous posting. But, you also are not asking a question here. what do you want to have done?

I'm old sorry I not doing things the way they should be. I'll report them and never post again.

 

[awoohaw]

I'm old sorry I not doing things the way they should be. I'll report them and never post again.

xl2bb can be tricky until you get the hang of it. I'm probably older than you.

But, it looks like you did the xl2bb properly here. You just didn't describe your problem.

 

[MARK858]

You just didn't describe your problem.

The OP has, but has pasted the XL2BB in the middle of it

@Robert Wyatt you are getting the VALUE# error because E13 isn't a blank cell, it has 3 space characters in it.

 

[Robert Wyatt]

The OP has, but has pasted the XL2BB in the middle of it
View attachment 109281
View attachment 109282



@Robert Wyatt you are getting the VALUE# error because E13 isn't a blank cell, it has 3 space characters in it.

I don't understand what do you mean by 3 space characters? E13 should be blank unless I have used PTO. J13 should be keeping track of Balance of PTO. If possible, can you tell me how to correct the problem?

 

[MARK858]

E13 isn't blank, you have 3 space characters in it (i.e. the spacebar has been pressed 3 times).

can you tell me how to correct the problem?

Click inside E13 and hit your backspace button 3 times then press enter (or click outside the cell)

 

[Robert Wyatt]

E13 isn't blank, you have 3 space characters in it (i.e. the spacebar has been pressed 3 times).

Click inside E13 and hit your backspace button 3 times

I tired what you said and it worked, but before you asked I tried placing 0 in the empty places and it worked as well. It's heck to get old. lol

 

[MARK858]

Happy you got it sorted and glad we could help

 

[awoohaw]

I tired what you said and it worked, but before you asked I tried placing 0 in the empty places and it worked as well. It's heck to get old. lol

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

Here is the Formula Evaluation tool: (I tried to screen shot the F9/Hover version but the screenshot program I have doesn't capture it).