Get number of pay periods

[kattlees]

We want to get projected income for our employees for a report. What I'm struggling with is getting number of pay periods so far.

We have 26 pay periods per year (every 2 weeks).

We have some instances where employees get 2 checks on a given date due to a bonus or an employee gets a check on a date that isn't a pay date. We only want to count the number of planned pay periods so if someone gets a check on a date that isn't a scheduled pay period, we don't want that to count.  If someone gets 2 checks on same date it would only count that date once.

I hope I'm making sense....

[parry2k]

Can you share sample data? 

[kattlees]

Example would be:

As of today we have had 21 pay periods and have 5 left.

Kathy has worked since the beginning of the year and has earned $10,000. We have had 21 pay periods since Jan. 1 so her projected income would be 10000/21*26 or 12380.95

Sue has worked since Aug. 15 and has earned $10,000. She has only received checks on 5 pay periods so her projected income would be 10000/5*10 or $20,000

Billie has worked since the beginning of the year and has earned $20,000. On May 4, a vacation day was missed so she received an extra check on May 10th.

Her projected income would be 20000/21*26 or 24761.9  (we don't count the may 10th into the number of pay dates)

[parry2k]

Great explanation, can you share how your data table look like? Is there only one table or there are multiple table. That will help get this going. May be just put all this example data in excel sheet with the columns you have in table and send it to me and I will get you back with a report 🙂

[kattlees]

Sure - does this work?

Columns are schedule pay dates except for May 10 which was the extra check.  I'm not sure how to attach an excel sheet.

[parry2k · Accepted solution]

Can you attach this so that I can download? or I will send you PM and you can email me. Thanks!

[kattlees]

wondering if you can answer another question for me on pay dates.

If I have an employee who is active, I want to show their projected income, if they are inactive, I need it to show their current year to date income (not project for future pay dates)

Every time I try to do an if statement
If(Is_Active=1,ProjectedIncome,CurrentySalary)

currenty salary is measure of sum(earned amount)

Projected income is a measure of (([Current year salary]-[Bonus])/[PayDatesDone])*[TotalPayDates]+[Bonus]

When I do this, I get some that say NaN, some say infinity and some are just outrageous high numbers.

Any ideas?