Using excel to calculate monthly salary for employees with different start dates

[sfhs40]

Good Morning

I am trying to create a salary model for departments to use to determine employee salary expense for the year for new hires. Each new hire will start at different times during the year (start date would be beginning of the month) to help them build their departmental budget. For example, if Matt had an annual salary of $100,000 and started March 1st, is there a formula that I could create that would allow the person using the budget tool to select March 1, 2021 from a drop down list and the formula would start the salary expense in March and carry through to December?

Thanks for any help

Dave

[Bernie Deitrick]

The logic for annual cost would be

=AnnualSalary*(12-MONTH(StartDate)+1)/12

IF you wanted to fill the 12 months with monthly costs, then something like

=IF(MONTH(StartDate)<=MONTH(HeaderDate),AnnualSalary/12,0)

The actual formula would depend on your worksheet structure, but for something like this
A B C D E F ..........
1 Name StartDate AnnualSalary Jan 2021 Feb 2021 .........
2 Fred 3/1/21 $100000

then in D2

=IF(MONTH($B2)<=MONTH(D$1),$C2/12,0)

copied across

[sfhs40]

Bernie

Thanks for the help. Much appreciated.

Dave

[Tinsu0424]

Dear Bernie

Kindly need your support for the below calculation the actualy amount is not working the formula

A B C D E F G H I J K L M N O
31 28 31 30 31 30 31 31 30 31 30 31
Name Start Date Salary 01-01-22 01-02-22 01-03-22 01-04-22 01-05-22 01-06-22 01-07-22 01-08-22 01-09-22 01-10-22 01-11-22 01-12-22
a 15-10-22 1 - - - - - - - - - 0 0 0
b 01-08-22 1 - - - - - - - 0 0 0 0 0
c 01-09-22 1 - - - - - - - - 0 0 0 0
d 01-10-22 1 - - - - - - - - - 0 0 0
e 01-11-22 1 - - - - - - - - - - 0 0

[Bernie Deitrick]

Try this file