Saving to Year To Date

[Robert Wyatt]

I trying to figure out how to get amounts under this period to transfer over to year to date Not sure how to do this I have a ytd sheet made up but don't know how to get it to save and transfer then keep adding eacch bi-weekly totals together can some one please help?

Time Keeper In The Works 02-03-2023.xlsm
	I	J
14	31.36
15	514.80
16	376.00
17	-
18	-
19	-
20	-
21	-
22	$ 922.16
PayStub

Cell Formulas
Range	Formula
I14:I21	I14	=ROUND(H14*G14,2)
I22	I22	=SUM(I14:I21)

 

[6StringJazzer]

You are just showing a few numbers, and it is not at all clear how this relates to biweekly totals or year-to-date sums. Please show something with dates that illustrates your whole problem.

 

[awoohaw]

This is a different way to do what I think you want. Entering items into the table will automatically update your calculations. (The YTD calculations are not filtered for the year in the date column, since I did not go into 2022).

Mr Excel Questions2.xlsm
	A	B	C	D	E	F	G	H
1
2
3	Date	Income/Expense Item	Amount			Last Period	2/5/2023
4	1/8/2023	Salary	5000			Salary	2500
5	1/8/2023	Taxes	500			Taxes	250
6	1/8/2023	Benefits	800			Benefits	400
7	1/22/2023	Salary	4000			Net Pay	1850
8	1/22/2023	Taxes	400
9	1/22/2023	Benefits	720			YearToDate
10	2/5/2023	Salary	2500			Wages	11500
11	2/5/2023	Taxes	250			Taxes	1150
12	2/5/2023	Benefits	400			Benefits	1920
13						Net Pay	8430
14
PayrollSummary

Cell Formulas
Range	Formula
G3	G3	=MAX(PayTable[Date])
G4:G6	G4	=SUMIFS(PayTable[Amount],PayTable[Date],$G$3,PayTable[Income/Expense Item],$F$4:$F$6)
G7,G13	G7	=SUM(G4,-(SUM(G5:G6)))
G10:G12	G10	=SUMIFS(PayTable[Amount],PayTable[Income/Expense Item],$F$4:$F$6)
A7:A12	A7	=A4+14
Dynamic array formulas.

 

[awoohaw]

I think this has the YTD taken Care of now:

Mr Excel Questions2.xlsm
	A	B	C	D	E	F	G
1
2
3	Date	Income/Expense Item	Amount			Last Period	2/5/2023
4	12/25/2022	Salary	2606			Salary	3,499
5	12/25/2022	Taxes	261			Taxes	350
6	12/25/2022	Benefits	326			Benefits	437
7	1/1/2023	Salary	4961			Net Pay	2,712
8	1/1/2023	Taxes	496
9	1/1/2023	Benefits	620			YearToDate	2,023
10	1/8/2023	Salary	3471			Wages	21,163
11	1/8/2023	Taxes	347			Taxes	2,117
12	1/8/2023	Benefits	434			Benefits	2,645
13	1/15/2023	Salary	2977			Net Pay	16,401
14	1/15/2023	Taxes	298
15	1/15/2023	Benefits	372
16	1/22/2023	Salary	3575
17	1/22/2023	Taxes	358
18	1/22/2023	Benefits	447
19	1/29/2023	Salary	2680
20	1/29/2023	Taxes	268
21	1/29/2023	Benefits	335
22	2/5/2023	Salary	3499
23	2/5/2023	Taxes	350
24	2/5/2023	Benefits	437
PayrollSummary

Cell Formulas
Range	Formula
G3	G3	=MAX(PayTable[Date])
G4:G6	G4	=SUMIFS(PayTable[Amount],PayTable[Date],$G$3,PayTable[Income/Expense Item],$F$4:$F$6)
G7,G13	G7	=SUM(G4,-(SUM(G5:G6)))
G9	G9	=MAX(YEAR(PayTable[Date]))
G10:G12	G10	=SUMIFS(PayTable[Amount], PayTable[Income/Expense Item],$F$4:$F$6, PayTable[Date],">=" & DATE($G$9,1,1), PayTable[Date],"<" & DATE($G$9+1,1,1) )
A7:A24	A7	=A4+7
Dynamic array formulas.

 

[awoohaw]

I think this has the YTD taken Care of now:

Mr Excel Questions2.xlsm
	A	B	C	D	E	F	G
1
2
3	Date	Income/Expense Item	Amount			Last Period	2/5/2023
4	12/25/2022	Salary	2606			Salary	3,499
5	12/25/2022	Taxes	261			Taxes	350
6	12/25/2022	Benefits	326			Benefits	437
7	1/1/2023	Salary	4961			Net Pay	2,712
8	1/1/2023	Taxes	496
9	1/1/2023	Benefits	620			YearToDate	2,023
10	1/8/2023	Salary	3471			Wages	21,163
11	1/8/2023	Taxes	347			Taxes	2,117
12	1/8/2023	Benefits	434			Benefits	2,645
13	1/15/2023	Salary	2977			Net Pay	16,401
14	1/15/2023	Taxes	298
15	1/15/2023	Benefits	372
16	1/22/2023	Salary	3575
17	1/22/2023	Taxes	358
18	1/22/2023	Benefits	447
19	1/29/2023	Salary	2680
20	1/29/2023	Taxes	268
21	1/29/2023	Benefits	335
22	2/5/2023	Salary	3499
23	2/5/2023	Taxes	350
24	2/5/2023	Benefits	437
PayrollSummary

Cell Formulas
Range	Formula
G3	G3	=MAX(PayTable[Date])
G4:G6	G4	=SUMIFS(PayTable[Amount],PayTable[Date],$G$3,PayTable[Income/Expense Item],$F$4:$F$6)
G7,G13	G7	=SUM(G4,-(SUM(G5:G6)))
G9	G9	=MAX(YEAR(PayTable[Date]))
G10:G12	G10	=SUMIFS(PayTable[Amount], PayTable[Income/Expense Item],$F$4:$F$6, PayTable[Date],">=" & DATE($G$9,1,1), PayTable[Date],"<" & DATE($G$9+1,1,1) )
A7:A24	A7	=A4+7
Dynamic array formulas.

These pay periods are weekly but it makes no difference the formulas select by date and not by a pay period.

 

[Robert Wyatt]

You are just showing a few numbers, and it is not at all clear how this relates to biweekly totals or year-to-date sums. Please show something with dates that illustrates your whole problem.

You are just showing a few numbers, and it is not at all clear how this relates to biweekly totals or year-to-date sums. Please show something with dates that illustrates your whole problem.

Hope this well show what I'm trying to do better

Time Keeper In The Works 02-03-2023.xlsm
	A	B	C	D	E	F	G	H	I	J
13	Earnings						Rate	Hours	This Period	YTD
14	Taxable Tip Hours						0.98	32.00	31.36
15	Stand In Hours						15.00	34.32	514.80
16	Regular Hours						11.75	32.00	376.00
17	Overtime Pay Rate						17.63	-	-
18	PTO Hours						11.75	-	-
19	Trainning						-	-	-
20	Holiday Pay						-	-	-
21	Incentive						-	-	-
22	TOTAL GROSS PAY:								$ 922.16
23	Per Taxes								This Period	YTD
24	Dental Insurance								21.00
25	Health Insurance								31.00
26	401K								92.22
27	TOTAL PRE TAXES:								$ 144.22
28	FEDERAL WITHHOLDINGS								This Period	YTD
29	Federal Income Tax Withholding								60.66
30	Louisiana State Income Tax Withholdings								19.30
31	Medicare Tax Withholdings								12.62
32	Social Security Tax Withholdings								53.95
33	TOTAL FEDERAL AND STATE WITHHOLDING:								$ 146.53
34	AFTER TAXES Deductions								This Period	YTD
35	Universal Life Insruance								14.79
36	402 K Loan #9								108.56
37	402 K Loan #8								174.31
38	TOTAL AFTER TAXES DEDUCTIONS:								$ 297.66
39	NET PAY:								This Period	YTD
40	Total Net Pay								$ 302.39
41	Other Benefits & Information								This Period	YTD
42	PTO Hours Available
43
44	Pay Summary								This Period	YTD
45	Earnings								922.16
46	Pre-Tax Deductions								144.22
47	Federal Taxable Wages								777.94
48	Social Security Taxable Wages								870.16
49	Medicare (HI) Taxable Wages								870.16
50	State Taxable Wages								777.94
51	Total Taxes								146.53
52	After-Tax Deductions								297.66
53	Net Pay								302.39
PayStub

Cell Formulas
Range	Formula
G14:H14	G14	=ROUND('Time Sheet'!B44,2)
G15	G15	=ROUND('Time Sheet'!B43,)
H15	H15	=ROUND('Time Sheet'!C43,2)
G16:H18	G16	=ROUND('Time Sheet'!B40,2)
I14:I21	I14	=ROUND(H14*G14,2)
I22	I22	=SUM(I14:I21)
I26	I26	=ROUND(Data!D9,2)
I27,I38	I27	=SUM(I24:I26)
I29:I32	I29	=ROUND(Calculator!C28,2)
I33	I33	=SUM(I29:I32)
I40	I40	=ROUND(I22-I14-I27-I33-I38,2)
I45	I45	=ROUND(I22,2)
I46	I46	=ROUND(I27,2)
I47	I47	=ROUND(I45-0*159.6-10%*I45-I24-I25,2)
I48	I48	=ROUND(I22-I24-I25,2)
I49	I49	=ROUND(I22-I24-I25,2)
I50	I50	=ROUND(I45-0*159.6-10%*I45-I24-I25,2)
I51	I51	=ROUND(I33,2)
I52	I52	=ROUND(I38,2)
I53	I53	=ROUND(I45-I14-I46-I51-I52,2)

Cells with Data Validation
Cell	Allow	Criteria
I24:I25	List	=Pre_Tax_Deductions
I27	List	=Pre_Tax_Deductions
A35:A37	List	=After_Tax_Deduction
I35:I37	List	=After_Tax_Amount
A24:A25	List	=Pre_Tax
A26	List	=Retirement_Deduction

 

[awoohaw]

What value(s) do you want taken to the YTD Page?

 

[awoohaw]

I'm guessing that you have one sheet like this for each employee? Is each workbook for one employee?
Are the worksheets completely Identical (Locations of of summary data and text comments) other than the data inputs?

 

[Robert Wyatt]

I'm guessing that you have one sheet like this for each employee? Is each workbook for one employee?
Are the worksheets completely Identical (Locations of of summary data and text comments) other than the data inputs?

Just for me I keep record of all my time worked I don't trust the payroll department lol. So I have a time sheet, Paystub built on my home computer I keep all the info in. It does all the deductions, but I can't seem to get it to do the year to date on a bi-weekly base's.

 

[awoohaw]

Okay, and will your summarized data always be A32:I40, and Net Pay in I41?
How do you name your sheets? With the Date of the End of the Biweekly Time Period? can you give some examples?