Vlookup

[Robert Wyatt]

I working up a Timesheet for bi-weekly time what I trying to do is where I have Est. Yearly Gross Wages are at the bottom right-hand side of the sheet I would like for it to figure the Est figure with the remaining pay period. Say pay period one is passed I would like it to figure the second pay period to the last. I have 26 pay periods I would like for each time I save a timesheet it would figure the next pay period to the last each time. Is there any way to do this?

New Blank Payroll Keeper 2024.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M
19	Weekday Date	Description	Days		Swings		Graveyards		Worked Hours	Regular Hours	Overtime Hours	PTO Hours
20			Time In	Time Out	Time In	Time Out	Time In	Time Out
22	Fri 12/15	Regular					11:55 PM	5:55 AM	6.00	6.00	-
23	Sat 12/16	Regular					11:55 PM	6:55 AM	7.00	7.00	-
24	Sun 12/17	Off							-	-	-
25	Mon 12/18	Off							-	-	-
26	Tue 12/19	Stand In					11:30 PM	8:00 AM	8.50	8.50	-
27	Wed 12/20	Stand In					11:30 PM	8:00 AM	8.50	8.50	-
28	Thu 12/21	Off							-	-	-
29								Total First Week Hours:	30.00	30.00	-	-
30
31	Weekday Date	Description	Days		Swings		Graveyards		Worked Hours	Regular Hours	Overtime Hours	PTO Hours
32			Time In	Time Out	Time In	Time Out	Time In	Time Out
34	Fri 12/22	Regular					11:55 PM	5:55 AM	6.00	6.00	-
35	Sat 12/23	Regular					11:55 PM	6:55 AM	7.00	7.00	-
36	Sun 12/24	Off							-	-	-
37	Mon 12/25	Off							-	-	-
38	Tue 12/26	Stand In					11:30 PM	8:00 AM	8.50	8.50	-
39	Wed 12/27	Stand In					11:30 PM	8:00 AM	8.50	8.50	-
40	Thu 12/28	Off							-	-	-
41								Total First Week Hours:	30.00	30.00	-	-
42	Total Bi-Weekly Hours		60.00
43
44					-
45
46
47
48	Description		Rate	Hours	This Period			Social Security Monthly & Yearly Information
49	Declared Tip Appliance		$0.98	26.00	25.48			Allowed Monthly Gross			$ 1,770.00
50	Regular Hours		$11.75	26.00	305.50			Estimated Monthly Gross			$ 1,596.99
51	Stand In Hours		$13.75	34.00	467.52			Differerance			$ 173.01
52	PTO Hours		$11.75	-	-			Allowed Yearly Gross			$ 21,240.00
53	Overtime Hours		$17.63	-	-			Estimated Yearly Gross			$ 20,760.92
54	Training Hours		$11.75	-	-			Differerance			$ 479.08
55	Meeting Hours		$11.75	-	-
56	Total Gross Earnings:				798.50			Year To Date Gross Earnings			$ -
Payroll Keeper

Cell Formulas
Range	Formula
J34:J40,J22:J28	J22	=ROUND(IF((OR(D22="",E22="")),0,IF((E22<D22),((E22-D22)*24)+24,(E22-D22)*24))+IF((OR(F22="",G22="")),0,IF((G22<F22),((G22-F22)*24)+24,(G22-F22)*24))+IF((OR(H22="",I22="",)),0,IF((I22<H22),((I22-H22)*24)+24,(I22-H22)*24)),2)
K34:K40,K22:K28	K22	=J22-L22
L22:L28	L22	=ROUND(MAX(IF('OT Work Sheet'!$C$5,MAX(0,SUM(K$21:K21)+J22-'OT Work Sheet'!$B$6),0),IF('OT Work Sheet'!$C$5,IF(J22>'OT Work Sheet'!$B$6,J22-'OT Work Sheet'!$B$6,0),0)),2)
B22	B22	=IF(ISBLANK(C22)," ",$D$14)
B23	B23	=IF(ISBLANK(C23)," ",$D$14+1)
B24	B24	=IF(ISBLANK(C24)," ",$D$14+2)
B25	B25	=IF(ISBLANK(C25)," ",$D$14+3)
B26	B26	=IF(ISBLANK(C26)," ",$D$14+4)
B27	B27	=IF(ISBLANK(C27)," ",$D$14+5)
B28	B28	=IF(ISBLANK(C28)," ",$D$14+6)
J41:M41,J29:M29	J29	=SUM(J22:J28)
L34:L40	L34	=ROUND(MAX(IF('OT Work Sheet'!$C$5,MAX(0,SUM(K$33:K33)+J34-'OT Work Sheet'!$B$6),0),IF('OT Work Sheet'!$C$5,IF(J34>'OT Work Sheet'!$B$6,J34-'OT Work Sheet'!$B$6,0),0)),2)
B34	B34	=IF(ISBLANK(C34)," ",$D$14+7)
B35	B35	=IF(ISBLANK(C35)," ",$D$14+8)
B36	B36	=IF(ISBLANK(C36)," ",$D$14+9)
B37	B37	=IF(ISBLANK(C37)," ",$D$14+10)
B38	B38	=IF(ISBLANK(C38)," ",$D$14+11)
B39	B39	=IF(ISBLANK(C39)," ",$D$14+12)
B40	B40	=IF(ISBLANK(C40)," ",$D$14+13)
D42	D42	=SUMIFS(J22:J40,C22:C40,"Regular")+SUMIFS(J22:J40,C22:C40,"Stand In")+SUMIFS(M22:M40,C22:C40,"FMLA")+SUMIFS(M22:M40,C22:C40,"PTO")+SUMIFS(M22:M40,C22:C40,"Vacation")+SUMIFS(F44,C44,"Training")+SUMIFS(F44,C44,"Meeting")
F44	F44	=ROUND(IF((OR(D44="",E44="")),0,IF((E44<D44),((E44-D44)*24)+24,(E44-D44)*24)),2)
E49	E49	=SUMIFS(J22:J40,C22:C40,"Regular")
E50	E50	=SUMIFS(J22:J40,C22:C40,"Regular")
E51	E51	=SUMIFS(J22:J40,C22:C40,"Stand In")
D52	D52	=ROUND(SUM(D50),2)
E52	E52	=SUMIFS(M22:M40,C22:C40,"PTO")+SUMIFS(M22:M40,C22:C40,"FMLA")+SUMIFS(M22:M40,C22:C40,"Vacation")
D53	D53	=ROUND(SUM(D50*1.5),2)
E53	E53	=ROUND(SUM(L22:L28,L34:L40),2)
D54	D54	=ROUND(SUM(D50),2)
E54	E54	=SUMIFS(F44,C44,"Training")
D55	D55	=ROUND(SUM(D50),2)
E55	E55	=SUMIFS(F44,C44,"Meeting")
L49	L49	=IF(ISBLANK(D14)," ",L52/12)
L50	L50	=IF(ISBLANK(D14)," ",F56*2)
L51	L51	=IF(ISBLANK(D14)," ",MAX(L49:L50)-MIN(L49:L50))
L52	L52	=IF(ISBLANK(D14)," ",VLOOKUP(I52,Settings!S1:T1,2,0))
L53	L53	=IF(ISBLANK(D14)," ",F56*26)
L54	L54	=IF(ISBLANK(D14)," ",MAX(L52:L53)-MIN(L52:L53))
F49:F55	F49	=D49*E49
F56	F56	=SUM(F49:G55)

Cells with Data Validation
Cell	Allow	Criteria
C22:C28	List	=Settings!$I$2:$I$8
C34:C40	List	=Settings!$I$2:$I$8
C44	List	=Settings!$K$2:$K$4

 

[awoohaw]

@Robert Wyatt , is there a particular reason you want to mix your data entry with the reporting?
Having a separate data page (start/end times/dates), and then another page to display your your calculations may make your problem easier to solve.

Also, is this one pay period? How many hours does the forum assume is a regular work week? What weeks is going to be the final week of the year? Is the last day of the year the ACTUAL last day of the calculations, or is the end of the pay period that the last day of the year is in, or is it the last day of the pay period that will have the last payday in the year? Please give more data.

 

[Robert Wyatt]

I work 30 hours a week 60 hours bi-weekly. Paid bi-weekly. I'm on Social Security is why I trying to make sure I don't go over the allowed limit of 21,240.00 a year. My time starts Fridays and ends on Thursdays. I clock in at 11:55 Pm and Clock out at 05:55 on Saturdays, I clock out at 06:55 am on Sundays. On Tuesdays and Wednesdays, I clock in at 11:30 pm and clock out at 08:00 am. My last pay period at the end of the year will be 12/14/2023. I went from 40 hours a week to 30 hours a week, but I'm still considered full-time.

 

[Robert Wyatt]

@Robert Wyatt , is there a particular reason you want to mix your data entry with the reporting?
Having a separate data page (start/end times/dates), and then another page to display your your calculations may make your problem easier to solve.

Also, is this one pay period? How many hours does the forum assume is a regular work week? What weeks is going to be the final week of the year? Is the last day of the year the ACTUAL last day of the calculations, or is the end of the pay period that the last day of the year is in, or is it the last day of the pay period that will have the last payday in the year? Please give more data.

I have 26 Pay Periods Time starts On Fridays and goes through Thursdays. I work 30 hours a week, I went from 40 hours to 30 hours cause I retired, but I still, work receiving Social Security. I'm still considered full-time at 30 hours a week. That way I can still get my Insurance through the Employer. I can't go over 21,240.00 a year or they will hold 3.00 dollars for every dollar I go over. That is why I am trying to fix up a program to keep a watch on it.

 

[Robert Wyatt]

I have figured it out, and have it working. I have fixed it to save it like my YTD pay stub. Thanks for all the help.