PTO Formula not working

[Robert Wyatt]

I'm including a cop of my file sorry I couldn't get the XL2BB to work I'm hoping someone can tell me where I'm going wrong with the formula. Let me say thought what I'm trying to do is have it show o until after I have been working for three years. then start recording. the formula I have installed just doesn't seem to be working right. any help would be much appreciated. never mind can't get my file to upload either

 

[Robert Wyatt]

Trying this one more time. I'm including a cop of my file sorry I couldn't get the XL2BB to work I'm hoping someone can tell me where I'm going wrong with the formula. Let me say thought what I'm trying to do is have it show o until after I have been working for three years. then start recording. the formula I have installed just doesn't seem to be working right. any help would be much appreciated.

PTO Accrual for Walmart.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			UNPROTECTED PTO		PROTECTED PTO		TOTAL				PROTECTED PTO		UNPROTECTED PTO		TOTAL
2	Original Date of Hire	Years of Service	Time To Earn 1 Hour	Max Hours/Year	Time To Earn 1 Hours	Max Hours/Year	Total			Years of Service	Time To Earn 1 Hours	Max Hours/year	Max Hours/year	Max Hours/Year	Max Hours/year
3										0-2	43.33	48	48	56	48
4										3	30	48	48	56	48
5										4-6	30	48	48	56	48
6		Description				Count				7-10	30	48	48	56	48
7		Total Hours Accrual				-				11-15	30	48	48	56	104
8		Total Paid Time Off Hours Used				-				16-19	30	48	48	56	104
9										20+	30	48	48	96	144
10
11
12	Pay Period Number	Pay Date	Total Hours	PTO	Sick	Vacation	Hours Accrual	Balance
13							Roll Over Balance:	0.00
14
15
16
17
18
19
20
21
Sheet1

Cell Formulas
Range	Formula
B3	B3	=IF(A3="","",(DATEDIF(A3,TODAY(),("Y"))))
C3	C3	=IF(B3="","",LOOKUP(B3,J3:J9,K3:K9))
D3	D3	=IF(B3="","",LOOKUP(B3,J3:J9,L3:L9))
E3	E3	=IF(B3="","",LOOKUP(B3,J3:J9,M3:M9/250))
F3	F3	=IF(B3="","",LOOKUP(B3,J3:J9,N3:N9))
G3	G3	=IF(A3="","",VLOOKUP(H13+100,H13:H39,1))
F7	F7	=SUM(G14:G39)
F8	F8	=SUM(D14:D39)
G14	G14	=IF(C14="","",LOOKUP($B$3,J3:J9,$M$3:$M$9/250*C14))
H14	H14	=IF(C14="","",IF(AND(ISBLANK(A14:D14)), " - ",IF(H13+G14-D14-E14-F14<$D$3,H13+G14-D14-E14-F14,144)))

 

[Dave Patton]

Some ideas that may help.
What formulas are not working for you?
I edited the Lookup table; please check edits.

T202312a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			UNPROTECTED PTO		PROTECTED PTO		TOTAL				PROTECTED PTO		UNPROTECTED PTO		TOTAL
2	Original Date of Hire	Years of Service	Time To Earn 1 Hour	Max Hours/Year	Time To Earn 1 Hours	Max Hours/Year	Total			Years of Service	Time To Earn 1 Hours	Max Hours/year	Max Hours/year	Max Hours/Year	Max Hours/year
3	01-01-05	18	30	48	0.192	56				0	43.33	48	48	56	48
4										3	30	48	48	56	48
5										15	30	48	48	56	104
6j

Cell Formulas
Range	Formula
B3	B3	=IF(A3="","",(DATEDIF(A3,TODAY(),("Y"))))
C3	C3	=IF(B3="","",LOOKUP(B3,J3:J7,K3:K7))
D3	D3	=IF(B3="","",LOOKUP(B3,J3:J7,L3:L7))
E3	E3	=IF(B3="","",LOOKUP(B3,J3:J7,M3:M7/250))
F3	F3	=IF(B3="","",LOOKUP(B3,J3:J7,N3:N7))

 

[Robert Wyatt]

when I put in the Hire date it shows #N/A instead of 0 I would like it to have a 0 not #N/A. and I'm not sure if about the Time to earn 1 hour is correct I not sure on how to figure that out when time to ear 1 hour is 43.33 or 30.

 

[Dave Patton]

1. "when I put in the Hire date it shows #N/A instead of 0 I would like it to have a 0 not #N/A"
Please clarify your question. N.B. You can post the example below into a clean sheet. Click the icon below the f(x) in the heading, move to your sheet, and paste into cell A1. Review the formulas with Excel's Formulas Evaluate Formula.

2. " . and I'm not sure if about the Time to earn 1 hour is correct I not sure on how to figure that out when time to ear 1 hour is 43.33 or 30."
Is the Lookup table correct? Please review and make appropriate edits. Years service 1 or 2 43.33 or years service 3 or more 30.
What information do you want in E3 and E4?

You can use the lookup formula that you prefer. My example shows Lookup and Vlookup.

T202312a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			UNPROTECTED PTO		PROTECTED PTO		TOTAL				PROTECTED PTO		UNPROTECTED PTO		TOTAL
2	Original Date of Hire	Years of Service	Time To Earn 1 Hour	Max Hours/Year	Time To Earn 1 Hours	Max Hours/Year	Total			Years of Service	Time To Earn 1 Hours	Max Hours/year		Max Hours/Year	Max Hours/year
3	01-Jan-21	2	43.33	48	??	56	48			1	43.33	48		56	48
4	01-Jan-00	23	30.00	48	??	56	104			3	30	48		56	48
5										15	30	48		56	104
6j

Cell Formulas
Range	Formula
B3:B4	B3	=IF(A3="","",DATEDIF(A3,TODAY(),"y"))
C3	C3	=IF(B3="","",LOOKUP(B3,J3:J7,K3:K7))
D3	D3	=IF(B3="","",LOOKUP(B3,J3:J7,L3:L7))
C4:D4,F4:G4	C4	=IF($A4="","",VLOOKUP($B4,$J3:$O5,COLUMN()-1,1))
F3	F3	=IF(B3="","",LOOKUP(B3,J3:J7,N3:N7))
G3	G3	=IF(B3="","",LOOKUP(B3,J3:J7,O3:O7))