Salary prorated

[le0245]

Hello, I'm new to this site and I'm currently looking for a way to calculate salaries and other related fields based on hire date and number of working days. I tried the different formulas on here and it's not working. I got a message that said "you've entered too many arguments for this function. How can I calculate monthly salaries based on a start date (cell F10) and using the network days formulas.

Thank you,
An

[JBeaucaire]

When you see that error and click OK, Excel puts the cursor in the formula in the area where the problem exists. In your formula you forgot the closing paren on the YEAR() function.

=IF($F88>EOMONTH(Q$12,0),$P88*(MIN(EOMONTH(Q$12,0),-MAX(Q$12,$F88)+1)/IF(MOD(YEAR(Q$12),4)=0,366,365)))

Why don't you manually demonstrate the answers you want to accomplish across the first row? That will help us see the goal and give us something to work toward with a single formula.

[le0245]

Hello JBeaucaire,
My original formulas: =if($F10>R$9,0,($H10*(1+$O10)*$I10*26/$AD$5*R$5)). It gave me the answer that I'm looking for but the hire date is 4/15/17, so I expect to see the salary in Aprilis $1,148 not $2,295. This formulas does not pro rate based on hire date. Also, I put the bracket on the year function and it gave me a big number in Jan (row 11).

Thank you for your help.

An

[JBeaucaire]

Maybe:
R10: =IF($F10>R$9,0,IF($F10<R$8,($H10*(1+$O10)*$I10*26/$AD$5*R$5),($J10/12/DAY(R$9))*(DAY(R$9)-DAY($F10))))

[JBeaucaire]

Or maybe:
R10: =IF($F10>R$9,0, ($H10*(1+$O10)*$I10*26/$AD$5*R$5) / IF($F10>R$8, DAY(U$9)/DAY($F10), 1))

[le0245]

Hello JBeaucaire,
It works. Thank you so much for your help. It took me whole day with this formulas and I'm still unable to come up with the right formulas. You're saved my day!

Happy Friday.
An

[JBeaucaire]

The proration still may not be accurate, though darn close, certainly.

Typically, to use NetWorkDays you create a reference list somewhere in your workbook where you list all the holidays specifically the you will honor. Then the Networkdays() function can utilize that list to give you an exact number of working days in any date range. So your prorations using that version of Networkdays would be 100% accurate.

[le0245]

Hi JBeaucaire,
I have another question related to the answer above. I change the hire date from 4/15/17 to 4/10/17 and I see the amount on April is $765. I thought the salaries would be increase from $1,148 (hire date 4/15/17,10 working days) to $1,721 (hire date 4/10/17, 15 working days).

Please help!

Thanks,
An

[le0245]

Hi JBeaucaire,
How can I use the Network days function for this formulas:

R10: =IF($F87>T$12,0,($H87*(1+$O87)*$I87*26/$AC$8*T$8)/IF($F87>T$11,DAY(T$7)/DAY($F87),1))

Thank you,
An

[JBeaucaire]

You would need to create the list of holidays. Extend the list out into the future as far as needed. The reference list can be two columns if you want to make it clear to your eye what the holidays are:

Please Login or Register  to view this content.

But the Networkdays formula is only going to use the dates in the second column.

[JBeaucaire]

I have no idea what that percentage increase thing is supposed to be doing, but what it looks to me you're initially trying to do is take the yearly salary and determine a daily rate using Networkdays to exclude weekends and holidays. Then the daily rate is paid each month only for the number of actually worked days.

Assuming the list of holidays is in column AS, then

AD5: =NETWORKDAYS($R$8, $AC$9,$AS:$AS )

R3:AC6 is now unneeded.

R9: =EOMONTH(R8,0) copied to the right

R10: =IF(MEDIAN(R$8,R$9,$F10)=R$9, 0, IF(MEDIAN(R$8,R$9,$F10)=R$8, ($J10/$AD$5) * NETWORKDAYS(R$8, R$9,$AS:$AS ), ($J10/$AD$5) * NETWORKDAYS($F10, R$9,$AS:$AS )))

[candybg]

I understand you wanting a formula to calculate salaries. However, there are other considerations, in addition to making Excel do the work for you.

In the US the definition of SALARIED employee is one that makes the same amount each week, regardless of the number of work days. Please verify this with your HR department or the US DOL. If you aren't in the US, you might need to check the laws/rules in your country.

I am recently retired from my full-time job of many years where I worked as CFO for a small agency. We only prorated the first pay and last pay of a salaried employee. All other pay periods were for the same amount of gross pay. This means salaried employees were employed for the full pay period, which includes weekend days and holidays. The prorating took this into account, so that someone hired on 4/3/17 would be an employee of the company for 28 days in that month, so their prorated salary would be 28/30 * salary.

Hope this helps!

[le0245]

Hello JBeaucaire,
Thank you so much for the formulas. It's working.

[le0245]

Hello Candybg,
Thank you for the info. I used the same method that you mentioned above in the previous company. I'm currently working at a new company and they have a different rules for the payroll budget.