Excel and Pay Periods

[Teallaura]

OK, so we really don't need to know the number of paydays, right?
If we know the number of work days between start date and end date, and we know the rate of pay per day, that makes it pretty easy, yes?

(yeah, some of this is rehash)

So, the NETWORKDAYS function uses the start and end dates to calculate how many actual work days there are.

Once we determine what the "daily rate" for a particular pay type is, we have an estimate (plus overtime/holidays, etc.)

Actually, it's the number of paydays that we do need. We're trying to estimate the MAGI or at least the AGI for people who've started or stopped jobs mid-year, or who held jobs in only part of the year. The system's calculator is useless - it multiplies as if the job began in January no matter what you tell it or it pulls last year's income from the previous application and inserts it as if it were still the correct income even if we've already changed something. It makes it a royal pain for folks who had breaks or changes in employment. I'm tired of having to count backwards on the calendar to try and work it out.

EG: Bob worked for McDonalds from January to March. He went to school from April to June then began working at Kohl's from mid-June to present. The stupid thing will multiply McD's as if Bob were working there the whole time. Then it will do the same to Kohl's in the block that matters but may or may not properly subtract the period from Jan to mid-June.



I want to be able to plug in the numbers and get a reasonable result. It doesn't have to be perfect but it can't be off by more than 1 - 2 %.

[Teallaura]

You can't just divide by number of days? 15 for bi-monthly, 14 for bi-weekly, 7 for weekly, 30 for monthly? Move to next largest number for incomplete periods.

That was my first thought but I couldn't figure out (or find a formula) how to deal with bi-monthly. 15 won't work, will it? It's only true for six of the months of the year (or five if you consider February).

[Teallaura]

one other consideration Teal...

Start date doesn't always determine the first pay date. Many places will want to sync the employee up with their standard pay day, so if you start the week of a payday, you would have to wait three weeks to get paid and from that point you would get paid every two weeks on the same day everyone else does.

I know - thanks! Most people know when they will get their first check if they haven't gotten it already. The few that don't - well, nothing's perfect...

[Teallaura]

Give me some sample data and what you want it to look like. I can put something together using VBA in Excel if someone doesn't beat me to it.

Thank you!!!!!

How much of a sample do you need?

[Carrikature]

That was my first thought but I couldn't figure out (or find a formula) how to deal with bi-monthly. 15 won't work, will it? It's only true for six of the months of the year (or five if you consider February).

365/15 = 24.333

Bi-monthly should be 24 periods, so that's pretty close. If you want to nail it, divide by 15.2083 instead.

[Sparko]

in the mcd, kohls example I am thinking you have to treat each as a completely separate person/job. Instead of one person with two jobs, you have to split it into 2 people with 1 job.

If I read your post right.

Then figure out the number of workdays in each job. Multiply that by their hourly rate and you would have their gross income for each job.
You would not really need pay periods, because everyone actually gets paid for every DAY they work, regardless of pay periods. If you start or quit in the middle of a week, you still get paid for those days you worked.

[Bill the Cat]

Thank you!!!!!

How much of a sample do you need?

about 5 or 6 individual situations and what you expect their outcome to be. For instance:

John Smith - Paid bi-weekly - $8.00 per hour - Dates of employ 3-15-16 to 5-1-16 Pay Dates: XXX Total earned: $XXXX.XX

[Bill the Cat]

in the mcd, kohls example I am thinking you have to treat each as a completely separate person/job. Instead of one person with two jobs, you have to split it into 2 people with 1 job.

If I read your post right.

Then figure out the number of workdays in each job. Multiply that by their hourly rate and you would have their gross income for each job.
You would not really need pay periods, because everyone actually gets paid for every DAY they work, regardless of pay periods. If you start or quit in the middle of a week, you still get paid for those days you worked.

That's what I was thinking. But if pay periods were included, just have a sum calculation cell based on the type of pay period and the daily rate

[Teallaura]

365/15 = 24.333

Bi-monthly should be 24 periods, so that's pretty close. If you want to nail it, divide by 15.2083 instead.

Okay, I get it now.

[Teallaura]

in the mcd, kohls example I am thinking you have to treat each as a completely separate person/job. Instead of one person with two jobs, you have to split it into 2 people with 1 job.

If I read your post right.

Then figure out the number of workdays in each job. Multiply that by their hourly rate and you would have their gross income for each job.
You would not really need pay periods, because everyone actually gets paid for every DAY they work, regardless of pay periods. If you start or quit in the middle of a week, you still get paid for those days you worked.

about 5 or 6 individual situations and what you expect their outcome to be. For instance:

John Smith - Paid bi-weekly - $8.00 per hour - Dates of employ 3-15-16 to 5-1-16 Pay Dates: XXX Total earned: $XXXX.XX

That's what I was thinking. But if pay periods were included, just have a sum calculation cell based on the type of pay period and the daily rate

The problem is the antiquated system we're stuck with - it asks for gross pay by pay period. If I could just force it to the correct figure we wouldn't need to work it out like this. But as it is, if they are in a detailed application, they have to enter gross per pay period and I need for the folks running the calculation to be able to do it without any mathematical gymnastics. I'm thinking it's simpler to use if the calculator uses the data the app actually asks for.


But at the end of the day it's the MAGI that we actually need for each job.

[Teallaura]

about 5 or 6 individual situations and what you expect their outcome to be. For instance:

John Smith - Paid bi-weekly - $8.00 per hour - Dates of employ 3-15-16 to 5-1-16 Pay Dates: XXX Total earned: $XXXX.XX

Although I really, really like this idea...

[Teallaura]

in the mcd, kohls example I am thinking you have to treat each as a completely separate person/job. Instead of one person with two jobs, you have to split it into 2 people with 1 job.

If I read your post right.

Then figure out the number of workdays in each job. Multiply that by their hourly rate and you would have their gross income for each job.
You would not really need pay periods, because everyone actually gets paid for every DAY they work, regardless of pay periods. If you start or quit in the middle of a week, you still get paid for those days you worked.

I gave both jobs as an example - a calculation of each individually is what we need because each will be listed individually.

[Sparko]

The problem is the antiquated system we're stuck with - it asks for gross pay by pay period. If I could just force it to the correct figure we wouldn't need to work it out like this. But as it is, if they are in a detailed application, they have to enter gross per pay period and I need for the folks running the calculation to be able to do it without any mathematical gymnastics. I'm thinking it's simpler to use if the calculator uses the data the app actually asks for.


But at the end of the day it's the MAGI that we actually need for each job.

Gross pay per pay period would just be Hourly rate x 8 for the daily rate. Then Day rate x Pay Period length (10, 15, 30, etc)

If you also know the total number of days they worked, and the pay period length, for example 2 weeks (10 days) you could divide the total number of days worked by the pay period and get the number of pay periods. If he worked 62 days, got paid every two weeks, he would have had 6 pay periods plus two days left over.

[Teallaura]

Gross pay per pay period would just be Hourly rate x 8 for the daily rate. Then Day rate x Pay Period length (10, 15, 30, etc)

If you also know the total number of days they worked, and the pay period length, for example 2 weeks (10 days) you could divide the total number of days worked by the pay period and get the number of pay periods. If he worked 62 days, got paid every two weeks, he would have had 6 pay periods plus two days left over.

Yeah, after I thought about it last night, I realized I'm probably over thinking it and decided to try your suggestions.


Thanks, guys!!!!