Sum Pay/Day/Month

B

Blake1918

New Member
Joined
Apr 8, 2014
Messages
5
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.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Start Date
[/TD]
[TD]End Date
[/TD]
[TD]# of Days off
[/TD]
[TD]Total Days worked
[/TD]
[TD][/TD]
[TD]Salary/day
[/TD]
[TD]As of Date
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 76"]
<colgroup><col></colgroup><tbody>[TR]
[TD]5-Feb-14
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<colgroup><col></colgroup><tbody>[TR]
[TD]15-Feb-14[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0
[/TD]
[TD]11
[/TD]
[TD]
[/TD]
[TD]$150.00
[/TD]
[TD]1-Feb-14
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 76"]
<colgroup><col></colgroup><tbody>[TR]
[TD]17-Feb-14
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<colgroup><col></colgroup><tbody>[TR]
[TD]22-Feb-14[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0
[/TD]
[TD]6
[/TD]
[TD]
[/TD]
[TD]$180.00
[/TD]
[TD]25-Apr-14
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 76"]
<colgroup><col></colgroup><tbody>[TR]
[TD]23-Feb-14
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<colgroup><col></colgroup><tbody>[TR]
[TD]9-May-14[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]9
[/TD]
[TD]67
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jan
[/TD]
[TD]Feb
[/TD]
[TD]Mar
[/TD]
[TD]Apr
[/TD]
[TD]May
[/TD]
[TD]Jun
[/TD]
[/TR]
[TR]
[TD]Salary
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
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.
 
Upvote 0
You made a valid point anthonya2369 we can break that current period for the salary increase.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Start Date
[/TD]
[TD]End Date
[/TD]
[TD]# of Days off
[/TD]
[TD]Total Days worked
[/TD]
[TD]
[/TD]
[TD]Salary/day
[/TD]
[TD]As of Date
[/TD]
[/TR]
[TR]
[TD]5-Feb-14
[/TD]
[TD]15-Feb-14
[/TD]
[TD]0
[/TD]
[TD]11
[/TD]
[TD]
[/TD]
[TD]$150.00
[/TD]
[TD]1-Feb-14
[/TD]
[/TR]
[TR]
[TD]17-Feb-14
[/TD]
[TD]22-Feb-14
[/TD]
[TD]0
[/TD]
[TD]6
[/TD]
[TD]
[/TD]
[TD]$180.00
[/TD]
[TD]25-Apr-14
[/TD]
[/TR]
[TR]
[TD]23-Feb-14
[/TD]
[TD]24-Apr-14
[/TD]
[TD]7
[/TD]
[TD]61[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]25-Apr-14
[/TD]
[TD]9-May-14
[/TD]
[TD]2
[/TD]
[TD]16
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jan
[/TD]
[TD]Feb
[/TD]
[TD]Mar
[/TD]
[TD]Apr
[/TD]
[TD]May
[/TD]
[TD]Jun
[/TD]
[/TR]
[TR]
[TD]Salary
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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.
 
Upvote 0
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.:eeek:

-Blake
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Prorating Salary based on hire date and Term date - by month
Replies
4
Views
868
KH Consulting
K
B
  • Question Question
calculate monthly value
Replies
0
Views
213
BrutalLogiC
B
C
  • Solved
Payroll overlapping Dates
Replies
2
Views
343
CaptArt
C
Z
  • Question Question
Dynamic Working and Week days remaining until end of month from a specific date in the month
Replies
3
Views
506
Peter_SSs
Peter_SSs
Z
  • Question Question
Sum Values by month from 1st day to last day of each month, and specific date range
Replies
6
Views
467
alansidman
A

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top