Payroll budget

[Sanjy2805]

Hi,

I am trying to create a payroll budget spreadsheet.

I have the following data

A,B,C
Employee Start Date, Employee End Date, Gross Salary

I want to then put in a monthly budget in month cells which are labeled November 2016, December 2016, January 2017 etc...

So for instance if I have an employee who had a start date on the 5/11/2016 and ended on the 29 December 2016, they should be paid the rest of the days in November and the 29 days in December. Each employee has different start dates and end dates.

Any help appreciated.

 

[MrJoosten]

Hello Sanjy,

I don't know if i understand your actual question. Maybe this can help you a little.

[TABLE="width: 339"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Start date[/TD]
[TD]End date[/TD]
[TD]Gross Salary[/TD]
[TD]Salary[/TD]
[/TR]
[TR]
[TD="align: right"]1-1-2015[/TD]
[TD="align: right"]1-4-2015[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]7397,26[/TD]
[/TR]
</tbody>[/TABLE]

formula i used:
=C2*(Datedif(A2;B2;"d")/365)

Hope it helps a little.

 

[RCBricker]

Hi Sanjy,

Not sure if MrJoosten has answered all of your request. It would seem to me that you wanted each month populated with the salary earned during that month. Each month header is the first of the given month. My data is set as follows

Start date End date Gross Jan Feb etc.

All pertinent values begin on row 2

So cell E1 has 01/01/2016 and in cell E2 would go the following formula

=IF(AND(MONTH(E$1)>=MONTH($B$2),MONTH(E$1)<=MONTH($C$2)),IF(MONTH($B$2)=MONTH(E$1),SUM(DATEDIF($B$2,EOMONTH($B$2,0),"d")*SUM($D$2/365)),IF(MONTH($C$2)=MONTH(E$1),SUM(SUM(DAY(DATE(YEAR(E$1),MONTH(E$1)+1,))-DATEDIF($C$2,EOMONTH($C$2,0),"d"))*SUM($D$2/365)),IF(AND(MONTH(E$1)>MONTH($B$2),MONTH(E$1)<MONTH($C$2)),SUM(DAY(DATE(YEAR(E1),MONTH(E1)+1,))*SUM($D$2/365))))),0)

(going to need to use the scroll bars to get the whole formula)


The first IF(AND checks to see if the current month (cell E1) is greater than or equal to your start date AND less than or equal to your end date.

It then checks to see if the current month (cell E1) is equal to the month of your start date

if so it then takes the number of days worked in that month and multiplies it against the gross salary divide by 365.

IF the above is not true it checks to see if the current month (cell E1) is equal to the month of your end date.

if so it then takes the number of days worked in that month and multiplies it against the gross salary divide by 365.

IF the above is not true it checks to see if the current month (cell E1) is greater than the month of your start date AND less than the month of your end date.

if so it multiplies the total number of days in the current month by the gross salary divided by 365.

IF the first IF(AND is not true then it returns a 0.

I am sure one of the formula gurus on here can do all this with a much simpler formula.