How to Cycle through 5 different Pay Scale Possibilities?

[Aegis Jones]

Hello All:

It's been a while since I've posted but I think I've got a good one for you guys. Long and short, I want to be able to change pay scales mid-month/year. The best way that I can think of is to have a cell designated for each pay period to set the pay scale and use a bunch of if statements to do what I'm needing.

Where the problem comes in is trying to configure the dates to match each pay scale. On the first page of the workbook, you'll see the pay scales I'm wanting to setup. The first 3 are the main focus but I believe the others can be done just as easily. Scale #5 is in there for the chance that an employer randomly selects set days in the month that an employee is paid.

If you look at the January sheet, you'll see the problem I'm having. So far, I've been trying to use If statements. (Which may end up being the best way to do it.) The reason that I need to have the schedule for each day of the week is because of the possibility of overtime.

I've been running my budget this way for years but I want to set a template that will configure this much for me each year so that I don't have to go in each year and manually build all of it. It's getting to be a pain. I'm wanting to establish a template that I can use each year and then provision everything else that I've already got built and automated as much as possible. (Not shown in the attachment.) For now the January sheet is the test sheet. Once it's figured out, then I'll duplicate it on the other sheet/months.

Any help would be greatly appreciated. Feel free to pm me.

Thank You,
AJ

[Bob Phillips]

Why does in January equal the start of the pay period 1 minus 11 days?

What are the yellow cells all about?

What exactly are you trying to do?

[Aegis Jones]

December 22nd is the last of the last pay period in 2008 for me. There's probably a better way to set that up but that was just the down and dirty way I did it.

The yellow cells are just there to highlight the pay scales and the areas where I'm having problems.

What I want to be able to do, is change pay scales mid year based on the pay scales outlined as 1 - 5 on the first sheet. The reason I need this is because for instance, if you're a contractor who gets paid weekly and then you get hired on permanent and you get paid bi-weekly. Well, mid month/year your pay scale changes. Same thing is likely to happen if you change jobs. In order to change the pay scales like that, each pay period is going to have to have the ability to be turned on/off to these 5 pay scales. Which pretty much means IF statements. I was wanting to see if anyone else had a better idea.

Whereas if you're paid weekly, some months you'll get paid 5 times. In 2009, those months are going to be Jan, May, July, and Oct. So, those months would need to be setup for 5 checks. If you were to change pay scales in April to what I've defined as Bi-Weekly 1, then July still needs an "extra" pay period because you'd get 3 checks that month vs. a normal Bi-Weekly month of 2 pay periods. But if you were changed to Bi-Weekly 2, May and Oct would be the months that you'd receive 3 pay checks. I want to configure the individual pay periods on the monthly sheets to adjust to the pay scale defined in B21 - N21.

I need to be able to reconfigure each pay period after a you change a pay scale on any given page. Like I said though, I'm pretty sure if statements are the way to go. I just wanted to throw this out there because maybe there's an easier way. Can you suggest a different design maybe? It'd probably be easier/better to set all of this up in Access but I don't have that and I know even less about Access than Excel. lol

-AJ

[rbrhodes]

Hi AJ,

Well I just spent hours on what I thought might be a simple (although interesting) problem!

Still not exactly sure what you want but take a look at this example. Only January is really set up and then only partially. Not so simple a problem!

[Aegis Jones]

DR,

Thank you for your help. When I started beating my head against it, I was like, well... Now what? lol It is kinda bear. :-)

This is so close to what I'm needing. There's a couple of things that I have questions/concerns about though.

The reason I had the scheduling page built out for a possilble 14 days for one check is because if the pay scale changes, say from weekly to bi-weekly, then you have more days on one check. Instead of 7 days possible, you'd have 14 days. Also, the date of the check needs to be at the top of the pay period for each.

So, say under Pay Scale 1, every month is built for 4 - 5 weeks per month then, you switch to Pay Scale 2. You'd only need 2 - 3 checks depending on which month. Well, on P.S. 1, the first 4 would be used (and 5 if needed). Under P.S. 2, the first 2 should be used (and 3 if needed). So, to that end, if you switched to P.S. 2, you'd need to report 14 days of work on that paycheck vs. 7 days. If we go back to the original file, on the January Sheet, A12 - A18 through D12 - D18 (and G12 - G18 if needed) would need to have the dates for a two week pay period. Because, if you switch, then you have a different number of days associated with each check. Then what's setup for checks 4 and 5 (columns J through N) would no longer be needed and I could either disregard or delete. This would also need to be able to handle changing pay scales mid month. So, if in January, after paycheck #1 (Columns A and B), you change pay scales to Bi-Weekly 1, then D5 - D18 would need to have the pay period for a full two week span. From that point forward, the other months would reconfigure for Bi-Weekly and adjust cells A5 - A18 through G5 - G18 to follow a bi-weekly pay scale. (Assuming that any month in question would need a third paycheck. Bi-Weekly 1 as you have it, wouldn't need to use the 3rd check until March.)

On the first page of yours (Pay Period Info), wouldn't cells C5 and C6 need to be swapped for chronological reasons? To where C5 was (=$D$1+7) and C6 was (=$D$1+14). Since I don't understand your formula exactly, would it cause it not to work if I swapped them? To follow up more on what I said in the previous paragraph, as the file you sent to me is setup, the date that's in Cell D5 on the January sheet would need to be in cell in Cell A12 if it is a bi-weekly pay scale. On a weekly pay scale, it would need to be where it is; D5.

-AJ

[rbrhodes]

Hi AJ,

You wrote:

"On the first page of yours (Pay Period Info), wouldn't cells C5 and C6 need to be swapped for chronological reasons? To where C5 was (=$D$1+7) and C6 was (=$D$1+14)."

- Actually, if cell D1 is the last 'Bi=Weekly' date then perhaps

Cell C5 formula should be =$D$1+14 (add 2 weeks to 'last Bi weekly') and

Cell C6 formula should be =$D$1+21 (add 3 weeks to 'last Bi weekly)

Does that make sense?

As for the layout changing, this could be done with VBA. I'd not gone there yet <g>. What I did was try to put in formulas that calculated totals per the pay schedule, ie:

- if PS was one, sum per week
- if PS was 2, only sum for 2 weeks
- etc

It's not actually working yet... and I didn't get as far as accounting for the pay scales changing as I didn't know if what I was attempting was what you were looking for exactly. So i just sent what I had.

I think I now see what you're after and I believe it will require some VBA as formulas can't change the actual sheet layouts.

I'll look at it some more...

[Aegis Jones]

The layout won't actually need to change though. Cells A12 though M18 could be turned off by an if statement ending with Value_False = "". That would leave the cell blank if needed. If the value were true, then it would calculate the next date until it reached row 18 in the respective column. Then, E5 would pick up, test to see what pay scale that pay period was on, calculate down to either E11 or E18.

For the months that are 2 paycheck months, it would stop at E18. So, H5 would be blank provided that the pay scale in E21 and H21 were both a bi-weekly pay scale. For the 3 paycheck months, H21 would need to be able to tell that there can be a 3rd check that month and automatically build cells H5 through H18. On a bi-weekly pay scale, there would never be a need for columns J - N so I could delete those columns or hide them.

For a weekly pay scale, 2009 has 5 paycheck months in January, May, July, and October. So, those would need all of the columns. The rest of the months would at most, need columns A - K.

[rbrhodes]

Hi,

Would the default be a Bi weekly 1 and then change as PS is entered?

[Aegis Jones]

Right now, I'd like to have the default pay scale to be weekly. The main reason being that it sets the maximum number of pay periods in a calendar year. I'd think that it's be easier to cut off the un-needed pay periods from the end of the month than to add new ones in.

-AJ

[Aegis Jones]

Bump to the top.

[Aegis Jones]

Ok, I've been working on this with a friend of mine and he and I have gotten it a lot closer to what it needs to be. I also have a better idea of how to word my questions. lol.

First, the pay scales have been changed a little bit from the original set on the first file I uploaded. That's ok, it just may call for another pay scale for a random set of dates as the pay days. (At this point, I'm not too worried about this option even though it is a possibility. I had to ask myself, "How often is that really going to happen anyway?" - You know?)

Second, the dates that I'm needing to show up on the monthly spreadsheets are the pay periods. So far, we've been building them to start with the actual pay days. And that's my fault because I wasn't wording my question correctly. Sorry guys. They actually need to be for the pay period plus one week of paycheck processing time before you receive it. In my workbook, I'm assuming one week as the processing time.

Third, my friend and I have gotten the first paycheck to cycle through all 5 of the pay scales and rebuild the later paychecks for the January sheet. Though, as I said, it's for the wrong dates.

Now there are four problems:

1. Correcting the dates. We used Dr's formula to start with but when we try to back up the date to the actual pay period start date (Pre 2009), it processes correctly but doesn't return the needed date.

2. Setting up the rest of the paychecks of the month to work with what has been built around Cell B4 on the January sheet of the new file. Meaning, E4, H4, K4, and N4 all have to co-exsist and work with B4.

3. Replicating the formulas to the other pages for the rest of the year.

4. Verifying that this will function no matter what year this template is built for going forward. So far, I see no good way to test without visually going through and confirming the dates like at the first of the month and the end of the month. And then confirming the first/last dates for the year.

Any ideas now that the ball of wax has grown?

-AJ

[rbrhodes]

Hi again,

Had an email server crash so I'm just getting back to all the places I've been...like here! I'm still looking at your sheet and am playing with some VBA to activate it all.

It's actually quite complicated! but I've made some progress...

[Aegis Jones]

Sorry to hear about your crash. That sucks.

Man, I'll tell ya, it has become a monster. A couple of us at the office are scratching our heads on this one. We've gotten it to work on the January sheet, first paycheck selection only. It's when we start looking at the next paycheck selection when it really starts to get hairy.

VBA would be the next step because then, it totally kills the possibility of running out of characters in a cell. (If my memory serves me correctly, the limit is 256.)

If it's all the same to you, just so that it's clear, I'd like to talk on the phone before you dive in too far into coding a bunch of VBA code. Mainly because I've been working on it with a friend from work for a couple of days and since I had such a hard time describing what I'm needing. You know? If you're interested, please feel free to pm me. Also, regular email works too. If you use gmail then gmail-chat could be helpful. (I work 2nd shift.)

The only other solution that I can see is to set tables for the now 6 payscales and run IF statements against the tables. This way the individual cells wouldn't have to actually calculate the dates or compare too many things and then calculate the dates. What do you think? Of course, a VBA script would make things nice a easy if you're up for it. I'd love to approach this from that angle.

I've attached a current copy of what we've worked on since last night.

-AJ

[Aegis Jones]

Now that I think about it, if we're talking about running VBA scripts, it's probably time that I bring up that there's two other worksheets that I normally build into this each year.

The reason I didn't bring them up before is because I figured that I'd be able to build templates for them on my own once the others were built. At this point though, I think that it's time to bring these up.

Attached is a basic model of what my budget is built upon each year. The new additions that haven't been brought up yet are how the income is processed against Expenses, Debts, and Savings. There are also two other worksheets titled Earnings and Savings. The Earnings sheet is there just to track the income of course. The Savings sheet is there to be able to track how much is spent in Expenses, how soon you can pay off debt accounts, and how much you're saving. (All on a per pay period time line.) Both of these sheets will need to be configured for the pay periods as well.

-AJ

[Aegis Jones]

Bump - To the top again.

[rbrhodes]

Hi AJ,

Well it is a monster. I've spent 50 hrs + on the problem and my code solutions are still buggy as hell. That's just the basic pay scales. Haven't touched the Bi Weekly 2 problem (start date change) other than putting in a msgbox where I thought it might affect the sheet, or the Random Bi- monthly date.

12 months X number of weeks X 5 pay scales makes for quite a matrix.

Here's an example:

[Aegis Jones]

Hello All:

Sorry to revive an old thread but we've still been working on it. This project has finally gotten to the point of replication. January works the way that we've intended it to work. (At least to the point to where we haven't seen it fail. lol) It needs to be tested by someone who hasn't been staring at it as long as we have. We're getting cross-eyed. lol

One of the problems at this point are replicating January to the other months of the year. If you look at January closely, you'll see that the problem we're having with replicating it is, the first pay period of the following month. It's being worked continuously by myself and a partner who may have already gotten past this. Still worth looking at though.

Also, we're having issues getting the 'Earnings' sheet to be able to keep up with the changes that are made on the monthly sheets. Likewise for the 'Savings' sheet. The 'Earnings' sheet is a vertical log for each pay period and the details for earnings. The 'Savings' sheet (probably could have a better name) will track horizontally the use of said earnings. This is so the user can track when they will meet a goal that they set for themselves. Be it pay of a certain debt account or save for a particular purpose, item, or goal. The 'Savings' sheet will also track what is allocated to the users expense accounts on the monthly sheets. All of this will, of course, be based on the pay periods determined by the drop downs on the monthly sheets.

Not all of the formulas are built yet for several obvious reasons. The ones that we have yet to build are:

1. Total up the hours for the respective pay periods.
a. Difficult because where do companies draw the O.T. line? (When does O.T. kick in?)
b. Each pay period will be dependent on the 'Earnings' page so it must be completed first.

2. On the 'Earnings' page, the difficulty we're running into is getting the dates to update properly with the monthly sheets. On the attached file you'll see where I've stopped because I'm not sure where to go from here. (My partner is building the monthly sheets so at this point, it may not be an issue.)
a. The other values on the 'Earnings' page will also need to update with the date is Column B. I have named 60 possible Pay Periods with the naming convention of: Jan 1 - 5, Feb 1 - 5 and so on. Hopefully that will make things easier by being able to point to one cell and test for the date that is in that cell.
b. This page is setup for a maximum of 52 pay periods and so far it is not intended for this to be dynamic. If it could be that would be nice but it definitely not required. lol. I'd like for it to also update the amount of rows used based on the pay scales selected on the monthly sheets.

3. The 'Savings' page can't be built yet because it will be pulling from the 'Earnings' sheet.
a. This page is split in identical halves because it is intended to be a log as well as a report. The top half will pull from the monthly sheets for the values entered in the appropriate pay period for the account. The bottom half will be a balance/payment report so that the user can track where any one account will stand throughout the course of the year.
b. This page could stand to be revised but as far as I can see, it's pretty good. Someone else take a crack at it please. :-)

4. The 'Ledger', 'Balance' and 'Ledger-Balance Test' sheets are a new addition to this project. The goal for these sheets is that the 'Balance' sheet will be able to update based on the transactions entered on the 'Ledger' sheet for the respective account. (Expense, Debt or Savings account.)
a. These two sheets are to be totally unrelated to the other sheets in the workbook. The ideology of these two sheets is completely separate and pretty much needs to remain that way. The closest that I would want to get to integrating them is to build a macro to run deposits from any pay period to the 'Ledger' so that the user does not have to manually type them in. The only reason they were added was to contain everything in one workbook.
b. The problem I'm having with these two sheets is outlined on the 'Ledger-Balance Test' sheet. I need to be able to update the balance of each account on the 'Balance' sheet based on a constantly updating list of transactions on the 'Ledger' sheet. Vlookup seems to not be an option because the data has to be in ascending order. Other options are needed.


I know this is a lot to chew through. I revived the thread mainly so that I could get everything down that I'm trying to do. Thank you in advance for your views and help.

-AJ