Forum Discussion
Split Weekly Payroll Numbers into Daily Amounts
I have a table with weely payroll amounts by person and I need to split those amounts into daily records for each person so I can accurate allocate costs to the appropriate month.
We use a simple formula; M-F = 1.0, Sat = 0.5 and Sun =0 so each weekly amount needs to be spread over 5.5 days.
How can I take each weekly total and create 6 daily records using the factors above?
I have a date table that also includes the paycheck date for each day if that helps any.
5 Replies
Hi,
Share a dataset and show the expected result.
Employee Start Date End Date Check Date Amount
John Doe 11/5/2018 11/11/2018 11/16/2018 $11,000.00
Bill White 11/5/2018 11/11/2018 11/16/2018 $5,500.00
Desired Result
Employee Date DayOfWeek Amount
John Doe 11/5/2018 Mon $2,000.00
John Doe 11/6/2018 Tue $2,000.00
John Doe 11/7/2018 Wed $2,000.00
John Doe 11/8/2018 Thu $2,000.00
John Doe 11/9/2018 Fri $2,000.00
John Doe 11/10/2018 Sat $1,000.00
John Doe 11/11/2018 Sun $ 0.00
Bill White 11/5/2018 Mon $1,000.00
Bill White 11/6/2018 Tue $1,000.00
Bill White 11/7/2018 Wed $1,000.00
Bill White 11/8/2018 Thu $1,000.00
Bill White 11/9/2018 Fri $1,000.00
Bill White 11/10/2018 Sat $ 500.00
Bill White 11/11/2018 Sun $ 0.00
