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.
<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
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 | |||
<colgroup><col></colgroup><tbody> </tbody> |
<colgroup><col></colgroup><tbody> </tbody> | 0 | 11 | $150.00 | 1-Feb-14 | |||
<colgroup><col></colgroup><tbody> </tbody> |
<colgroup><col></colgroup><tbody> </tbody> | 0 | 6 | $180.00 | 25-Apr-14 | |||
<colgroup><col></colgroup><tbody> </tbody> |
<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