payroll year to date help

[Robert1960]

I'm making a payroll time sheet in Excel 2007 with a pay stub I'm trying to get it to keep a year to date can some one explain to me how to make the year to date work. as of right now I do not have anything in the column for the year to date as I'm not sure how to do it. Please see attachment.

[aydeegee]

Hi Robert1960

Welcome to the forum.

How you store YTD figures will depend on how you intend to use the spreadsheet. I attach one such example.

Let me know if this will work for you.

Regards
Alastair

[Robert1960]

Can you tell me to make it keep the first year to date total and pick it up on the next pay period and add it to the new time sheet. when I use the blank time sheet I save as and use the pay period ending date to save it. I need to be able to have the year to date saved on the blank so that each time I use it for a new pay period it will add the new total to the total from the last pay period.

[aydeegee]

Hi Robert1960

Start the year with nothing in columns U onward. The YTD will then be the same as this period.

To move to the second period, you need to do a "save as" (eg Period1 / date / or something else unique). (See Note below). Go back to your original sheet and copy the data from ytd to column U, but paste values only.

YTD will then be this period plus Column S

To move to the third period do another "save as" and then on the original sheet you have to decide. Either (a) copy YTD to column W or (b) insert a new column to the left of U and copy there. (a) is (slightly) easier BUT you have to ensure each period that the formula in column S includes all relevant columns. (b) the formula is automatically correct - which is why I always use (b).


Note - at the end of the year you will end up with your "active" sheet plus 26 period sheets - Seems a lot, but you need a way to get back to each period's payslip.


Hope this helps

Regards
Alastair

[Robert1960]

sorry to keep asking but can you tell me how to make the year to date stay one the original sheet with out me having to add it each time I make a new time sheet?

[aydeegee]

Hi Robert1960

No need to apologise - it is important that you understand.

I think I see your problem. You should not be making a new sheet at all.

Start with a sheet that we will call Time Card. Enter the details (and this will be period 1) in any column you need, but not columns P&Q (Incidentally, I do not use merged cells for data - it can get complicated). As it is period 1, columns U onward are blank.

This month and YTD are now the same. Save Time Card and save as "Time Card Period 1" (or some such title). You have now completed Period 1.

To start Period 2, go to Time Card, copy all "this period" figures and paste values only to column U (you might want to leave the calculation of the net payable as a formula, just in case something goes wrong). Remove all figures from "This period". The YTD figures should now be the same as Period 1 YTD. Complete Period 2 figures. The YTD figures will now be updated.

Save Time Card and save as "Time Card Period 2"

To start Period 3, go to Time Card, insert a column to the left of U, copy all "this period" figures and paste values only to (the new) column U . Remove all figures from "This period". The YTD figures should now be the same as Period 2 YTD. Complete Period 3 figures. The YTD figures will now be updated.

Save Time Card and save as "Time Card Period 3"

Having written this out, it seems very complicated, but once you see the logic and get used to it, it is not so bad.


To confuse you further, it might be that instead of copying the current period to column U, you might prefer to use YTD figures - in which case you will not need 26 columns of data in U onwards - just one. It depends on what you prefer.

Feel free to ask further questions.

Regards
Alastair

[Robert1960]

If i could figure out how to send a new attechment I would put the new program up so you could see it then maybe i could understand it better...lol please for give me i am a true blonde. lol

[aydeegee]

Hi Robert1960

To send an attachment, go to reply and go advanced. Scroll down to the Manage attachments and click on the box "Manage attachments". Click on Add files and then on Basic uploader. Browse to the file that you wish to upload and click on open. Then click on upload. Close down that window and the file you want to upload will be left. Click on submit reply.

Regards
Alastair

[Robert1960]

OK hopefully the new time sheet program is attached...you can see where i am trying to get ytd to keep track. I need it to automatically record it each time i make a new time sheet

[aydeegee]

Hi Robert1960

Your workbook with the sheet YTD works OK. Once you have completed a period, save it and then copy this period's data to the YTD (as values, not formulas). You will then need to clear the data from the time sheet so the figures on the pay stub will be zero in this period and YTD from the period just finished.

Because you are deleteing data from the timesheet, I would recommend saving a copy of that period, otherwise you will not have b able to refer back to that period's timesheet. Of course this may not be a problem for you.

Hope this helps. Let me know if not.

Regards
Alastair