Yeah, I tried that as the obvious answer and it jacks up the spreadsheet still. Odd. Not a big deal. I just deleted the State Tax transaction after I paste in the rows. Works well!
All my amounts under column AX are goofy dates like 2/9/1895. I see =IFERROR( on every column in row 4. The only amount that is correct is column AX, row 4, which is the gross paycheck.
The Configure Individual Transactions section is beautiful. 
Maybe delete and start over?
Generally you donât need to worry about the formatting in section 4, everything you copy from there will take on the formatting in your transactions sheet. Thatâs not to say you canât go in and add formatting to make it look nice (I canât do that ahead of time because I donât know what order your columns are in), but the formatting you add there wonât affect the transactions in the Transactions sheet since youâll be pasting them in âas values onlyâ, which means without formatting, so they match everything else there. Have you tried copying any transactions yet to see if they paste OK?
Holy cow. It worked! Fantastic!
Thank you
1 Like
@jpfieber After using for the month of March, Iâve suddenly realized that this breaks the Account Reconciliation module (I think). Not sure how you are supposed to balance your checking account when using this? Gross Pay obviously doesnât show up as a deposit on your bank statements and now Net Pay is categorized as a negative number and a Transfer so basically, I have no deposits listed in the Account Reconciliation tool now.
Am I missing something here?
Account Reconciliation does look at Transfer transactions since they affect the balance of the account, so it will see the Net Pay deposit which will correspond to a change in balance of the same amount. The negative Net Pay transaction is from a different âmade upâ account that you wouldnât be using Account Reconciliation for. I use both of these templates, so I know they work together. If yours isnât working right, Itâs likely something to do with the configuration of Paycheck Deduction Transaction Generator. If I had to guess, Iâd say that you might have set the âAccountâ value in AO6 to the actual bank account where your check gets deposited. You donât want to do that, instead, this should be a made up string like âACME Paycheckâ that does not match any of your existing accounts. Itâs really only here to give you something to sort/filter your Transactions sheet by should you want to see only paycheck transactions. If you left that field blank, things would still work.
I never actually filled in AO6-AO8 on the Paycheck module. Left them blank. So thatâs one problem so Account Reconciliation is looking for all transactions for that Account and isnât seeing them to pull them in.
However, once I fix that how are you getting around the Net Pay now being a negative number and not showing as a Deposit in Account Reconciliation? Confused by that.
So in Paycheck module in AV and AW I have the following where these line items are positive or negative numbers. Net Wages is then coming over as a negative number in Account ReconcilationâŚ
Gross Wages +
Net Wages -
Social Security -
Medicare -
Federal Tax - Withholding -
AO6 - AO8 are optional, you can continue to leave them blank and they wonât affect how things are working. The negative net pay that is generated by Paycheck Deduction Transaction Generator wonât affect Account Reconciliation because the negative net pay transaction is not a transaction from the account being reconciled (the Account column of the Transactions sheet doesnât show your bank account). There should be a net pay transaction in your bank account that is positive of the same amount as the negative net pay transaction. Thatâs the transaction that affects your account balance and is seen by Account Reconciliation. The main reason we generate a negative net pay transaction is because if we didnât, your budget would see the Gross Pay transaction we generate, and it would see the net pay transaction in your bank account, so it would show you has having over twice as much income as you really do. We need to âcounteractâ the net pay transaction, but we donât want to delete it since itâs necessary to keep your bank account accurate. Having a negative transaction of the same amount takes care of this so your budget sheets all balance properly.
Doh! I deleted that positive Net Pay deposit.
Life is hard when you are dumb!
2 Likes
LOL, none of us our dumb. We are just at different learning stages on any given topic!
1 Like
Thank you @jpfieber for the tool. Iâm having some issues with the transaction dates in AP. All the other data looks great. Ideas? Suggestions?
âPayDateâ on B is set at 1/13/2023
âDateâ to be copied into transactions in AP show 44939
Iâve deleted and re entered the dates, but canât seem to fix it.
If you just copy/paste (paste values only) into the Transactions sheet, they should format fine. I and others have noticed the same thing.
2 Likes
As @sfergus1 said, once the transactions are pasted into your Transactions sheet they will pick up the formatting you use there. I didnât pre-format the Transaction Generator sheet since some people add columns or move them around, so thereâs no way of knowing ahead of time what format a column should be. You can format them in the generator sheet if you like, but it makes no difference to the end result.
2 Likes
Really handy sheet. I had one question on deductions which are retirement contributions. For example my 401(k) is a negative paycheck âdeductionâ, but really itâs a positive retirement contribution. Why do you have those added as ânegativeâ? Should I just add them as positive values?
My retirement contributions show up twice in my transactions. Once as a transfer out from my gross paycheck, but then as a positive transfer to my retirement account when the money arrives into my Fidelity account. If you really wanted to get precise, I suppose youâd want to break it down between your contribution to your retirement account (an optional expense) and your employerâs contribution (which is additional compensation).
If you have your 401k account in Tiller, and it provides transactions, you could set up your paycheck deduction as a transfer, and then set the 401k accounts transaction of those funds coming in as a transfer. I donât track that, so Iâm just counting the deduction as an expense in the retirement category which ends up being offset by the increase in balance on my retirement accounts.
Thanks - that makes sense
I am having an issue with the template. The net total shows as a negative. However, the actual numbers are correct.
This is as designed. Net should be negative since it will offset the positive bank deposit transaction to zero them out so the Gross total is the one that shows up as your pay. Without the negative net, your Gross shows up as positive, and your bank deposit shows up as positive, so your income will be almost doubled and obviously throw your reports and budgets off.
I made a small tweak to the template. They adjusted which check some of my deductions are taken out, so this month many deductions I normally have werenât removed. In the template this resulted in a number of the deductions being â$0â, which is unnecessary to add to the Transactions template. I added a filter so those $0 deductions donât show up in the âPaycheck Deduction Transactionsâ section that you copy from. The version is now 1.3 and will be available if you want to update (you donât have to update if you donât think youâll encounter this issue) after @randy pushes the change.
Issue: Filter $0 deductions
Version: 1.3
1 Like