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)

 

[Robert Wyatt]

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?

When I save it to file I save it as Next Pay Period Number.

 

[awoohaw]

Can you give an example of the last two or three sheet names you have called the pay periods?

Are you comfortable with cutting and pasting into a table? You already have the monthly summary in a column/flat file format a cut and past of the summary data into a table, and then building your visualizations is easier and give you more flexiblility.

 

[Robert Wyatt]

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?

I use Save Next pay period with new name it all ways put the next pay period number on it.

 

[awoohaw]

I'm asking for an example of what it looks like, not how you save.
Is it "PP20230125" or "PayDate Jan 25 2022"
But that is moot if you are comfortable with the second part of my last post.

 

[Robert Wyatt]

I'm asking for an example of what it looks like, not how you save.
Is it "PP20230125" or "PayDate Jan 25 2022"
But that is moot if you are comfortable with the second part of my last post.

it would look like this Pay Period 1 01-01-2023

 

[awoohaw]

Robert, what version of excel do you use?

 

[Robert Wyatt]

Robert, what version of excel do you use?

Excel Office 2019

 

[Fluff]

Excel Office 2019

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Robert Wyatt]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

up dated

 

[awoohaw]

Okay Here is a solution. It will require some work on your part
The solution will involve you copying and pasting information that is calculated below your paystub replica into a summary worksheet that has a table of the paystub line items. Below are the paystub replication and the summary worksheet.

This next step is not necessary, but it will help ensure you're date and replica paystub are matching:
(If you don't want this, then delete cell E55, and remove conditional formatting from cells B55:E55.)
You'll need to copy this custom function into a module in your VBA editor.
Press ALT-11, Open the Project Explorer (if it is not already open from the menu or Cntl-R),
Find VBA Project ("your workbook name"), right click, Click the arrow beside "Insert" then MODULE.
You should see a Module open under that line in the Project Explorer pane.
Select that module, double click or Press F7, it will take you to the code window for that module.
Paste this into the Module:
Function ThisSheetName() As Variant
Dim sn As Variant
sn = ActiveSheet.Name
ThisSheetName = sn
End Function

If you have more than 8 possible line items you'll need to update the formulas that feed the "copy" grid area AND move that entire section so it will not be affected by new rows.

In the summary worksheet you must convert your data to a TABLE (Cntrl T).
Paste the data onto the summary worksheet in the first row under the table.
The format of the pasted data should match the table's formatting.

You can now make pivot tables and all other summarizations based on all of your data.

Mr Excel Questions2.xlsm
	A	B	C	D	E	F	G	H	I	J
13	Earnings						Rate	Hours	This Period	YTD
14	Taxable Tip Hours						32	31.36
15	Stand In Hours						15	34.32	514.8
16	Regular Hours						11.75	32	376
17	Overtime Pay Rate						17.63	-	-
18	PTO Hours						11.75	-	-
19	Trainning						-	-	-
20	Holiday Pay						-	-	-
21	Incentive						-	-	-
22	TOTAL GROSS PAY:								890.8
23	Per Taxes								This Period	YTD
24	Dental Insurance								21
25	Health Insurance								31
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.3
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								890.8
46	Pre-Tax Deductions								144.22
47	Federal Taxable Wages								749.72
48	Social Security Taxable Wages								838.8
49	Medicare (HI) Taxable Wages								838.8
50	State Taxable Wages								749.72
51	Total Taxes								146.53
52	After-Tax Deductions								297.66
53	Net Pay								302.39
54	PayPeriod as a Date
55
56
57	Copy this section as values into the summary workbook in the first row under the table:
58	With Some More Recent, Non 365 Versions				With 365
59
60
61
62
63
64
65
66
67
68
69
70
Pay Period 1 01-01-2023

Cell Formulas
Range	Formula
I22	I22	=SUM(I14:I21)
I27,I38	I27	=SUM(I24:I26)
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)
E55	E55	=IF(A55<>"",IF(ISNUMBER(A55)=FALSE, "Please enter a valid date",IF(TEXT(MONTH(A55),"00")&"-"&TEXT(DAY(A55),"00")&"-"&TEXT(YEAR(A55),"0000")=RIGHT(ThisSheetName(),10),"","The Date in the Worksheet Name Does Not Match.")),"")
A59	A59	=IF(OR(ISBLANK(A55),A55=""),"",TRANSPOSE(MMULT($A$55,TRANSPOSE(ROW(B59#)^0))))
B59	B59	=IF(OR(ISBLANK(A55),A55=""),"",$A$45:$A$52)
C59	C59	=IF(OR(ISBLANK(A55),A55=""),"",$I$45:$I$52)
E59	E59	=IF(OR(ISBLANK(A55),A55=""),"",HSTACK(TRANSPOSE(MMULT($A$55,TRANSPOSE(ROW(B59#)^0))),$A$45:$A$52,$I$45:$I$52))

Mr Excel Questions2.xlsm
	A	B	C
1	Line Item	Pay Period	Amount
2	After-Tax Deductions	11/20/2022	250
3	Earnings	11/20/2022	950
4	Federal Taxable Wages	11/20/2022	950
5	Medicare (HI) Taxable Wages	11/20/2022	950
6	Pre-Tax Deductions	11/20/2022	175
7	Social Security Taxable Wages	11/20/2022	950
8	State Taxable Wages	11/20/2022	950
9	Total Taxes	11/20/2022	150
10	After-Tax Deductions	12/4/2022	275
11	Earnings	12/4/2022	750
12	Federal Taxable Wages	12/4/2022	750
13	Medicare (HI) Taxable Wages	12/4/2022	750
14	Pre-Tax Deductions	12/4/2022	150
15	Social Security Taxable Wages	12/4/2022	750
16	State Taxable Wages	12/4/2022	750
17	Total Taxes	12/4/2022	165
18	After-Tax Deductions	12/18/2022	295
19	Earnings	12/18/2022	1100
20	Federal Taxable Wages	12/18/2022	1100
21	Medicare (HI) Taxable Wages	12/18/2022	1100
22	Pre-Tax Deductions	12/18/2022	165
23	Social Security Taxable Wages	12/18/2022	1100
24	State Taxable Wages	12/18/2022	1100
25	Total Taxes	12/18/2022	170
26	After-Tax Deductions	1/1/2023	297.66
27	Earnings	1/1/2023	922.16
28	Federal Taxable Wages	1/1/2023	777.94
29	Medicare (HI) Taxable Wages	1/1/2023	870.16
30	Pre-Tax Deductions	1/1/2023	144.22
31	Social Security Taxable Wages	1/1/2023	870.16
32	State Taxable Wages	1/1/2023	777.94
33	Total Taxes	1/1/2023	146.53
34
35
36
37
38
PayPeriodSummary