Monthly Pro Rated Salary Based on start and end date [SOLVED]

Hi everyone,

I need help with my excel file.

GOAL: to compute monthly prorated salary based on the inputs placed on the budgeted salary, start date and end date. The worksheet should automatically compute the monthly salary and should be based on the # of days of that month until the end date and put 0 on the months when employee is no longer employed (not within the start and end date).

I need this file to compute salary per month pro rated based on # of days and taking into account the exact start date and end date.

Attached is my file.

PROBLEM: The problem with my file is although it computes the monthly salary and puts in 0 in months employee is not employed, the salary calculation is not "to the dot". It does not calculate based on the number of days within the month. the budgeted salary only gets spread evenly thoughout the year and does not take into account the exact start date and end dates.

I know that my formula is using 12 month spreading, I cannot figure out what to use when I want it daily (probably salary divided by 365??)


Thanks and more power to this forum!!

Perhaps:

Please Login or Register  to view this content.

the above calculates % of days worked in month and uses that as multiplier against annual / 12

Originally Posted by thomas.mapua

Is there a way to alter the formula so that, if the end date cell is blank it is being read as the employee is still employed, and therefore will still compute pro rated monthly until I put something in the end date to trigger it to stop computing?

Should be doing that already, no ? Post a revised sample with formula in place to illustrate.

Modify per below

Please Login or Register  to view this content.

Thank you so much for this formula.

Is there an easy way to incorporate a salary increase from a specific date?