Simple Staff cost budget - 12 months forecast - start/end dates

[Alex007]

https://www.dropbox.com/s/n1qkx709foyzokd/Staff budgets.xlsx?dl=0

Hi All,

I need some help. Which formula would i use to?:

If start date cell is blank = calculate a monthly salary
If end date cell is blank = calculate a monthly salary
If start date has a date = calculate only from the start date and every month onwards
IF end date has a date = calculate only up to end date.

I have this =IF(OR($B4>L$2,AND($C4<L$2, $C4<>0)),0, $H4/12) but is there a way to apportion monthly salary daily if the end/start date is not 01st of the month. currently the formula divides the total cost by 12 (H/12). so if the start date is on 2nd of the month it gives me zero value at the moment ?

Can someone please help me.

 

[oldbrewer]

					MONTH of	Jun 2015
			start	end	salary/month	salary this month
		fred	10/06/2015		1000	700.0
		bill			1000	1000.0
		tom		20/06/2015	1000	666.7
			formula giving 700
	=IF(AND(F13="",G13=""),(30/30)*H13,IF(G13="",($I$8+30-F13)/30*H13,(G13-$I$8+1)*H13/30))
	the 30 really needs to be automated
	either by a 12 month lookup table
	or a formula for days in the month

<colgroup><col span="3"><col span="2"><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[Alex007]

thanks oldbrewer. its what i want but i need the formula to change with the each month. at the moment if i drag it securing start/dates/salary cells it is coming out with interesting numbers.

I need the number to move from jun15 to may 16.

thanks for your help again.

 

[oldbrewer]

surely my start and end dates drive this - if nothing changes next month fred and bill get full salary - either you delete row for tom or have a flag that says he is off the payroll

 

[Alex007]

surely my start and end dates drive this - if nothing changes next month fred and bill get full salary - either you delete row for tom or have a flag that says he is off the payroll

i am working on budgets. cant delete a row. i need the cell to come out with full time salary for the rest of the year it currently doesn't.

 

[oldbrewer]

name	started	left	salary	01/01/2015	01/02/2015	01/03/2015	01/04/2015	01/05/2015	01/06/2015	01/07/2015	01/08/2015	01/09/2015	01/10/2015	01/11/2015	01/12/2015
tom	03/05/2013	25/01/2015	1000	806.45161	0	0	0	0	0	0	0	0	0	0	0
bill	03/05/2013		1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000
dave	15/01/2015		1000	548.3871	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000
				formula for the 806.45162
				which was dragged across and down
				=IF(AND($C4="",$B4<e$3),$d4,if($c4="",if($b4<e1,$d4,((f1-$b4) 31)*$d4),if($c4<e$3,0,(((31-($f3-$c4-1))="" 31)))*$d4))<="" td=""></e$3),$d4,if($c4="",if($b4<e1,$d4,((f1-$b4)>
				excel forum hits " < " lol
				'=IF(AND($C4="",$B4 < E$3),$D4,IF($C4="",IF($B4 < E1,$D4,((F1-$B4)/31)*$D4),IF($C4 < E$3,0,(((31-($F3-$C4-1))/31)))*$D4))

<tbody>
</tbody>