Payroll/Pay Stub Help

[WorldBuilder]

Hi all,

OK, I admit it... I am terrible with Excel. Here's the deal, and I hope someone can gimme a hand. We have hired a nanny. And we are paying her appropriately, legally, above board. So we have to take out taxes and keep "notes", etc... A friend of mine helped me create what is attached. I really can't continue to lean on him so I wanted to ask here and see if I could achieve a level of understanding myself.

What I want to do is simple... Have the sheet (2015) be a place where I simply plug in the numbers. Once we roll into 2016, I'd like that to continue. Then on the "Printout" sheet, each week, that is the nanny's pay stub. I want it to show her gross and net wages, YTD totals, and pay period and pay date.

Now, when we first hired her, we kind of screwed up because we would pay her for the very week she worked. Of course, in the real world you get paid for the prior week. We "fixed" that as of October 16th, which is why that row is in bold. But for some reason, and I don't think it's related, the automated filling-in of data on the "Printout" sheet no longer works. On that sheet, also, my buddy had created some wacky formula to get the dates right back when we were paying her same week. I don't mind filling that out manually from now on, but if there is a way to automate it, that'd be great.

So in a nutshell, I want this "form" so I can plug in numbers. Then I want that to automatically fill out the pay stub part to be able to print and give to her for her records.

Help? Please? Thanks!

Chris

[Richard Buttrey]

Hi, and welcome to the forum.

Does the attached help?

Assuming it does and you're comfortable with it post back again and we can probably help further. For instance there's no real need to have a second sheet for 2016. We can show you how to continue with just the current 2015 sheet so that all data is kept on one sheet.

[WorldBuilder]

YES!

Wow, thanks! Well, us the dates seem to be the only issue remaining. In the file you posted, for example, the PAY DATE should be 11/20/2015 (tomorrow) and the pay period should be 11/9/2015 - 11/13/2015. And in actuality, I'd prefer the stub to say "WEEK ENDING" with a single date. So PAY DATE would be 11/20/2015 for WEEK ENDING 11/13/2015.

I would certainly be happy to type that all manually each week if necessary, but is there a way for the file to auto-update itself re: the dates?

And yes, I'd love to know how to continue years on a single sheet, so long as it's still possible to separate YTD totals and what-not. That all make sense?

You rock, Rich. Thanks!

CB

[Richard Buttrey]

Hi,

See the attached which is now corrected.

I've also deleted the 2016 sheet since you can merely continue adding 2016 numbers when the time comes underneath the 2015 numbers. The printout doesn;t need the row 24 Yearly totals numbers since cells K10, K11 & K15 work out the YTD from all the values for the year in question. They no longer need a summary row.

[WorldBuilder]

Hi Rich,

THANKS!

Ok, so you're saying that I can simply continue into 2016 (and beyond) on the "data" sheet, and yet the totals on the printout will, for lack of a better term, reset to $0 on January 1st 2016 and start over? If correct, I'd love to know how it's doing that! Magic! :-)

Also, on "Printout" cell K6, the pay period is wrong.

In the file you posted, for example, the PAY DATE should be 11/20/2015 (tomorrow) and the pay period should be 11/9/2015 - 11/13/2015. And in actuality, I'd prefer the stub to say "WEEK ENDING" with a single date. So PAY DATE would be 11/20/2015 for WEEK ENDING 11/13/2015.

How can I fix this, kind sir?

CB

[Richard Buttrey]

Hi,

I wasn't sure what you meant by 'the stub' but I believe the attached contains all the relevant descriptions and if they're not in quite the layout you want just move the formulae around.

The key to all this is the hidden cell J1 which contains the current week ending date. This cell is also used to work out which year to accumulate for the Year to Date totals. Hence you can just keep adding dates at the bottom of the Data Sheet

[WorldBuilder]

Thanks! Ah, yes... This makes sense now. I am still a tad fuzzy on the formulas and values, but at least I can see it now and should be able to figure it out IF I ever even need to. This was very helpful, Rich, and I can't thank you enough!

CB

[Richard Buttrey]

My pleasure and thanks for the rep.

[WorldBuilder]

My pleasure and thanks for the rep.

Ooh, I found a minor issue and I'm not sure how to fix it. Here's the file again, after I just entered the pay for this particular week. Issue has to do with dates. The "Printout" shows "PAY DATE 11/20/2015 for WEEK ENDING 11/13/2015". It SHOULD say "PAY DATE 11/27/2015 for WEEK ENDING 11/20/2015". I think I forgot to mention something that evidently matters.

We never know what day we will actually pay her with a check, because we never know, until last minute, when her last day of the week will actually be.

For example, today is the 25th, and we are paying her today, but we want the "Printout" to say the 27th (Friday). The payment today is for LAST week, which ended on the 20th.

I hope I am explaining this right. Help, please? Thanks!

CB

[Richard Buttrey]

Hi,

See changed file attached.

[WorldBuilder]

Sweet, thanks! So will this offer the correct date no matter the day on which it is filled out? If so, cool. If not, what do I need to know. And finally, can you explain just how this date formula works? I really can't thank you enough.

CB

[Richard Buttrey]

Hi,

The formula in J1 of the Printout is

Please Login or Register  to view this content.

The INDEX() function takes three arguments
1. A data range
2. A row number
3. A column number

and returns the value in the data range for where the data row and column numbers intersect.

The Match function requires at least 2 arguments and an optional third
1. A value to look for
2. A column in which the value will be found
3. An optional TRUE/FALSE argument which dictates whether an EXACT Match is found or the nearest match. The default if no 3rd element is specified is the Nearest value

and returns the row number nearest to the value being looked for.


In the J1 Index formula the Data range is Data!B:B
The row number is given by the MATCH function which finds today's date in column A of the Data sheet. But because the 25 November date doesn't exist and because there is no third TRUE element in the Match it finds the nearest date that is less than today's date, i.e. 20 November which is to be found in row 17 of the data column A.

The column number of the INDEX function is set to 1 since the Index Data range is only one column wide.

So all in all the INDEX() function finds row 17 column 1 of the Range (Data!B:B) i.e. 27 November.

The K1 formula is similar except that the Row number for the INDEX function which is given by the MATCH function is reduced with a -1

The system will work provided the dates are always in ascending order.

[WorldBuilder]

Hello again,

UGH! Well, I never touched it, but just went to do this week's pay for her and, well, see attached. The "Printout" is showing all "N/A" with formula errors. Huh? I never touched it, how did I break it? LOL!

CB

[WorldBuilder]

Hi again. Sorry to keep bothering you, but do you have any ideas about what's wrong?

[jjhayes]

I think what is happening is that the "N/A" is because the match in cell J1 on the Printout tab is pointing to cell B21 on the DATA tab. That cell is currently blank. This is the paydate for the week ending 12/18/15. Until that paydate is filled in you will see "N/A".

[Richard Buttrey]

Hi,

I think you've skipped a week.

Enter 18 December in B20 and 25 December in B21 and you should be OK.

[WorldBuilder]

OK, yeah. Thanks! I had to add 12/15 to B21 as you said. 12/18 in B20 was already there. So, it seems as though the pay date of the upcoming week always needs to be in place first? I guess I just have to get used to how this works so I know the process and how not to screw it up. LOL! Thanks!

[WorldBuilder]

OK, this is weird. There is something wrong (again) and I totally can't figure out what it is. SORRY for the continual bother.

Today is 1/1/16 and I just paid her for the week ending 12/25/15. Upon filling in the information in Row 22, the Printout" sheet went completely "N/A". So I did as previously suggested and added Fri, Jan 8, 2016
to B23. The math is all right, AFAIK, but the dates are wrong on Printout and as a result the rate, hours, and totals aren't appearing. It says "PAY DATE 1/8/2016 for WEEK ENDING 1/1/2016". Try the attached, you'll see.

Am I using this wrong? Do I have to fill this out BEFORE Friday each week? I'm not sure what the problem is. You guys have been most gracious with your help. Thank you.

CB

[Richard Buttrey]

Hi,

The J1 formula needs a "-1" adding. i.e.

Please Login or Register  to view this content.

See attached.

[WorldBuilder]

Hi Richard,

Thanks! That seemed to work for this week. I just did it and didn't have to fill in B24 with the upcoming Friday to get the Printout to work. So the way this is formulated now, does this mean that it can only be filled out on Fridays? Would it be screwed up if it were done on, say, a Wednesday?

CB