I am trying to provide payment information for a client on a per day basis in Excel. My current payment report has a line for each payment and that payment has a "pay from date" and a "pay through date". For example a payment dated today may have a payment from date of 11/2/17 and a pay through date of 11/8/17.
My client is requesting to have a line for each day of that particular payment for that specific employee. So in the example above instead of one line with a payment of $700 for 11/2/17-11/8/17 we would want 7 lines and each line would be for 1 day of the pay from and through period.
I also need to split out the payment amount among the number of days. In my example on each of the 7 lines I would have a payment amount of $100.
Below is an example of what my original date would look like and what I need the final output to look like. I'm trying to find a way to automate the final report in anyway possible.
Thank you so much for any assistance this great group can provide!!!
<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>
My client is requesting to have a line for each day of that particular payment for that specific employee. So in the example above instead of one line with a payment of $700 for 11/2/17-11/8/17 we would want 7 lines and each line would be for 1 day of the pay from and through period.
I also need to split out the payment amount among the number of days. In my example on each of the 7 lines I would have a payment amount of $100.
Below is an example of what my original date would look like and what I need the final output to look like. I'm trying to find a way to automate the final report in anyway possible.
Thank you so much for any assistance this great group can provide!!!
ORIGINAL DATA | |||||||
Claim Number | EE ID | EE First Name | EE Last Name | Payment Date | Pay From Date | Pay Through Date | Payment Amount |
12345611 | 897045 | Susie | Smith | 11/9/2017 | 11/2/2017 | 11/8/2017 | $700 |
End Result of Customer Request - Trying to Automate | |||||||
Claim Number | EE ID | EE First Name | EE Last Name | Payment Date | Pay From Day | Pay Through Date | Payment Amount |
12345611 | 897045 | Susie | Smith | 11/9/2017 | 11/2/2017 | 11/2/2017 | $100 |
12345611 | 897045 | Susie | Smith | 11/9/2017 | 11/3/2017 | 11/3/2017 | $100 |
12345611 | 897045 | Susie | Smith | 11/9/2017 | 11/4/2017 | 11/4/2017 | $100 |
12345611 | 897045 | Susie | Smith | 11/9/2017 | 11/5/2017 | 11/5/2017 | $100 |
12345611 | 897045 | Susie | Smith | 11/9/2017 | 11/6/2017 | 11/6/2017 | $100 |
12345611 | 897045 | Susie | Smith | 11/9/2017 | 11/7/2017 | 11/7/2017 | $100 |
12345611 | 897045 | Susie | Smith | 11/9/2017 | 11/8/2017 | 11/8/2017 | $100 |
<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>