Sum Pay/Day/Month

[Blake1918]

Hey everyone,

Newb here, I am currently looking for a monthly payroll formula. I looking through inter-webs, though I'm have not been able to find one applicable to my situation.

The scenario is:
1st table "Start Date"; "End Date"; "# of Day Off"; "Total Days Worked"
2nd table "Salary/Day", "As of Date"
3rd table Spans the Months by Pay of that Month

Example: The 3rd table would return in the month of Feb ("collective days worked"-"days off")*(Salary at that time)=$3450.00

The days off can be taken from the first/last or staggered through out the time worked.

Start Date	End Date	# of Days off	Total Days worked		Salary/day	As of Date
5-Feb-14 <colgroup><col></colgroup><tbody> </tbody>	15-Feb-14 <colgroup><col></colgroup><tbody> </tbody>	0	11		$150.00	1-Feb-14
17-Feb-14 <colgroup><col></colgroup><tbody> </tbody>	22-Feb-14 <colgroup><col></colgroup><tbody> </tbody>	0	6		$180.00	25-Apr-14
23-Feb-14 <colgroup><col></colgroup><tbody> </tbody>	9-May-14 <colgroup><col></colgroup><tbody> </tbody>	9	67
	Jan	Feb	Mar	Apr	May	Jun
Salary

<tbody>
</tbody>

The formula I us to find the days worked YTD is:
=IFERROR(IF(SUMPRODUCT(Table2[[#All],[Start Date]]=Table2[[#All],[End Date]]),1,SUMPRODUCT(Table2[[#All],[End Date]]-Table2[[#All],[Start Date]]-Table2[[#All],[# of Days off]]+1)),1)

Any information the community could provide to help shine light on the matter would be greatly appreciated.

Regard,
-Blake

 

[Blake1918]

I meant to include, I am running 2007 on mac.

 

[anthonya2369]

I am confused by the data here for 23-Feb-14 to 9-May-14 but the salary/day changed effective 25-Apr-14. How do you assume the days worked/off and the salary to apply since the salary/day changed 2 weeks before the end date? Knowing how you want that treated would help.

 

[Blake1918]

You made a valid point anthonya2369 we can break that current period for the salary increase.

Start Date	End Date	# of Days off	Total Days worked		Salary/day	As of Date
5-Feb-14	15-Feb-14	0	11		$150.00	1-Feb-14
17-Feb-14	22-Feb-14	0	6		$180.00	25-Apr-14
23-Feb-14	24-Apr-14	7	61
25-Apr-14	9-May-14	2	16
	Jan	Feb	Mar	Apr	May	Jun
Salary

<tbody>
</tbody>

 

[anthonya2369]

The next problem with your data collection is that you are trying to do the salary by month but the 23-Feb to 24-Apr covers three different months and would be hard to decipher which months get the 61 pay days and which get the non-paid days off. Also, it may be easier to have each salary calculated (if possible) on that line and then use sumifs to get it to the proper month accumulation.

 

[Blake1918]

Could we average the days off over the duration of the time worked? It would be preferable to not to calculate salary per entries then using sumifs, but I will take any resolutions at this point.

-Blake

 

[anthonya2369]

I am still trying to think of a way that works. I do think that having hard stops for each month will be beneficial as well as when salaries change. SO I would recommend the data be split

23 Feb 2014 to Apr 24 2014

to

23 Feb 2014 to 28 Feb 2014
01 Mar 2014 to 31 Mar 2014
01 Apr 2014 to 24 Apr 2014

Then you could use sumifs to count the number of days and multiply by the applicable salary.

 

[Blake1918]

This is what I have so far, it's not pretty but it is doing the job. The only thing left is selecting the correct pay for that time frame.
And of course if anyone has a more efficient why of going about doing this. (I think I am hovering around 1800 characters for this)
E2=Wage
F10=Current Month

=(E2)*(SUMPRODUCT(--(MONTH(Table2[[#All],[Start_Date]])=MONTH(F10)),--(MONTH(Table2[[#All],[End_Date]])=MONTH(F10)),Table2[[#All],[End_Date]]-Table2[[#All],[Start_Date]]-Table2[[#All],[#_Off]]+1)+SUMPRODUCT(N(MONTH(Table2[[#All],[End_Date]])<>MONTH(Table2[[#All],[Start_Date]])),N(MONTH(Table2[[#All],[Start_Date]])=MONTH(F10)),--(DATE(YEAR(Table2[[#All],[Start_Date]]),MONTH(Table2[[#All],[Start_Date]])+1,0))-(Table2[[#All],[Start_Date]])+1)+SUMPRODUCT(N(MONTH(Table2[[#All],[End_Date]])<>MONTH(Table2[[#All],[Start_Date]])),N(MONTH(Table2[[#All],[End_Date]])=MONTH(F10)),(Table2[[#All],[End_Date]])+1-(--(DATE(YEAR(Table2[[#All],[End_Date]]),MONTH(Table2[[#All],[End_Date]]),1))))+SUMPRODUCT(N(MONTH(Table2[[#All],[Start_Date]])<MONTH(F10)),N(MONTH(F10)<MONTH(Table2[[#All],[End_Date]])),N((MONTH(Table2[[#All],[End_Date]])-MONTH(Table2[[#All],[Start_Date]]))>1))*(EOMONTH(F10,0)+1-(F10))-SUMPRODUCT(--(MONTH(Table2[[#All],[End_Date]])<>MONTH(Table2[[#All],[Start_Date]])),--(MONTH(Table2[[#All],[Start_Date]])=MONTH(F10)),--(DATE(YEAR(Table2[[#All],[Start_Date]]),MONTH(Table2[[#All],[Start_Date]])+1,0))-(Table2[[#All],[Start_Date]])+1,Table2[[#All],[#_Off]]/(Table2[[#All],[End_Date]]-Table2[[#All],[Start_Date]]+1))-SUMPRODUCT(N(MONTH(Table2[[#All],[Start_Date]])<MONTH(F10)),N(MONTH(F10)<MONTH(Table2[[#All],[End_Date]])),N((MONTH(Table2[[#All],[End_Date]])-MONTH(Table2[[#All],[Start_Date]]))>1),(Table2[[#All],[#_Off]]*(EOMONTH(F10,0)+1-(F10))/(Table2[[#All],[End_Date]]-Table2[[#All],[Start_Date]]+1)))-SUMPRODUCT(--(MONTH(Table2[[#All],[End_Date]])<>MONTH(Table2[[#All],[Start_Date]])),--(MONTH(Table2[[#All],[End_Date]])=MONTH(F10)),((Table2[[#All],[End_Date]])+1-(--(DATE(YEAR(Table2[[#All],[End_Date]]),MONTH(Table2[[#All],[End_Date]]),1)))),Table2[[#All],[#_Off]]/(Table2[[#All],[End_Date]]-Table2[[#All],[Start_Date]]+1)))

Thank you for being patient with me,
-Blake