šŸ† Paycheck Deduction Transaction Generator

Google Sheets Show & Tell
,
21

Fairly new to the community here so please forgive me if Iā€™ve missed something. Upon setting up my feed I immediately noticed a need to track these deductions. I have looked for a template to do so and failed to find anything. Is there a place I could grab a copy of what you have built?

22

Hi, the link to the template is at the bottom of the first post.

23

I think this might do the trick. The issue is that your date is going in as a text, not date.
I changed the formula in AO5 from a vlookup to a lookup that gets the last row of data and it comes in as a date:

=iferror(lookup(1,ArrayFormula(F5:F/F5:F),F5:F))

Change AO9 to this:

=AO5-DAY(AO5)+1

and AO10 to this:

=AO5-WEEKDAY(AO5,1)+1

and Categorized Date and Date Added should simply be AO5.
then just verify the formatting on CB and AS.

1 Like
24

Thanks! I read through everything and still missed that.

25

Is there any way to format the cells so that these are automatically negative values?

26

Also, you can get rid of your helper data area by using FILTER in columns AT and on.

=FILTER(AH5:AL,AI5:AI<>0)

I am in middle of Excelorizing the sheet and many of the formulas work in both. Using the filter eliminates the need for the helper stuff.

27

Itā€™s best not to since the formulas are expecting it a certain way and it would require extensive rewrite.

1 Like
28

This has been a huge help in tracking actuals. Thanks for your work in building the template!
Being limited in knowledge I have found that I can eventually get to something like this, it simply takes an EXTENSIVE amount of time.
As of now the set defaults to the latest payroll entry; is there a way to populate with a selected entry? I am playing catch up and have already entered numerous dates.

29

Thanks for the kind words. You can enter as many past and future paychecks as youā€™d like, and it should look for the most recent paycheck (the PayDate that is closest to but not greater than today).

30

All of your suggestions are great! Iā€™m still learning with all this QUERY and ARRAYFORMULA stuff, so I figured there was a better way of doing things, but wasnā€™t sure how. Hopefully Iā€™ll get time this weekend to work some of them into the template!

1 Like
31

Meaning that it will only populate section 4 with the most recent entry correct?

32

Correct. Section 1 is where you enter all the paycheck data, and Section 4 is where it is formatted into transactions that you can copy to your Transactions sheet.

33

The Date Override works great!

This is a great tool and will really help in tracking true expenses in one place. I canā€™t thank you enough!

1 Like
34

Just came across another challenge.
I had if there are multiple paychecks paid on the same date only one transaction is populating. Of course you can just change the date(s) of deposit in Section 1 then, once copied to the transaction sheets revert to the correct deposit date. Is it possible to have the Pachecks tool use only single line items instead of summing all of one date?

35

Thatā€™ll be a harder challenge. Let me give that some thought.

36

Iā€™ve worked around it for now, but I do have multiple sources of income so this may be a recurring issue.

37

I added an option in Section 3 called ā€˜Date Overrideā€™. The dropdown shows a list of all the PayDates youā€™ve entered so far. Choosing one will ā€˜overrideā€™ the default option of choosing the most recent PayDate. This is useful if youā€™re just starting out, and have a bunch of prior paychecks you want to generate transactions for.

38

With multiple sources of income, there would be many differences to contend with.

  • Different deductibles
  • Different ā€˜Accountā€™
  • Different ā€˜Institutionā€™
  • Possible date conflicts

Though Iā€™m sure itā€™s possible to make this sheet handle all those possibilities, itā€™s going to add a lot of complexity. In the end, I think the best solution would be to have one sheet per source of income. You can rename the sheets to anything you like, and each one can be specific to that source of income.

1 Like
39

That makes sense. Iā€™m not too worried about all the extras like accounts and institutions as that will be tracked with the banking information already. The specific issue I ran into was with Bonus pay. It is normally issued as a separate check on the same date as normal payroll. Donā€™t go out of your way for me. I only bringing this up incase someone else in the community finds this tool just as useful as I do. Figured it might be worth some fine tuning, though it is already a great resource!

40

What you can do is simply enter one paycheck, transfer it over to the transaction sheet, and then do the second bonus payout.