Hello,
hopefully my topic isn't too confusing.
But I basically have a total column and weekday (Mon-Sun) column with values and a time range column.
I need to split the total column based on the time range (whether the time range expands beyond the month)
As an example:
Below is my input data
ID | Time-Period | Mon | Tues | Wed | Thurs | Fri | Sat | Sun | Total |
ABC-123 | 8/31/2020 - 9/6/2020 | 5 | 3 | 4 | 0 | 0 | 0 | 0 | 12 |
ABC-155 | 8/31/2020 - 9/6/2020 | 4 | 5 | 3 | 2 | 5 | 0 | 0 | 19 |
DDE-123 | 8/31/2020 - 9/6/2020 | 1 | 2 | 3 | 2 | 13 | 0 | 0 | 21 |
FEE-123 | 8/31/2020 - 9/6/2020 | 7 | 8 | 0 | 4 | 6 | 0 | 0 | 25 |
ABC-123 | 9/7/2020 - 9/13/2020 | 6 | 12 | 43 | 5 | 6 | 0 | 0 | 72 |
ABC-155 | 9/7/2020 - 9/13/2020 | 45 | 3 | 55 | 6 | 3 | 0 | 0 | 112 |
DDE-123 | 9/7/2020 - 9/13/2020 | 57 | 4 | 5 | 7 | 6 | 0 | 0 | 79 |
FEE-123 | 9/7/2020 - 9/13/2020 | 4 | 2 | 4 | 5 | 8 | 0 | 0 | 23 |
ABC-123 | 9/14/2020 - 9/20/2020 | 6 | 46 | 3 | 3 | 9 | 0 | 0 | 67 |
ABC-155 | 9/14/2020 - 9/20/2020 | 3 | 32 | 5 | 4 | 0 | 0 | 0 | 44 |
DDE-123 | 9/14/2020 - 9/20/2020 | 5 | 44 | 7 | 5 | 0 | 0 | 0 | 61 |
FEE-123 | 9/14/2020 - 9/20/2020 | 54 | 32 | 8 | 76 | 3 | 0 | 0 | 173 |
ABC-123 | 9/21/2020 - 9/27/2020 | 5 | 76 | 9 | 98 | 2 | 0 | 0 | 190 |
ABC-155 | 9/21/2020 - 9/27/2020 | 6 | 5 | 0 | 0 | 56 | 0 | 0 | 67 |
DDE-123 | 9/21/2020 - 9/27/2020 | 75 | 4 | 6 | 0 | 43 | 0 | 0 | 128 |
FEE-123 | 9/21/2020 - 9/27/2020 | 32 | 3 | 53 | 5 | 22 | 0 | 0 | 115 |
ABC-123 | 9/28/2020 - 10/4/2020 | 44 | 2 | 34 | 6 | 4 | 0 | 0 | 90 |
ABC-155 | 9/28/2020 - 10/4/2020 | 67 | 4 | 3 | 8 | 5 | 0 | 0 | 87 |
DDE-123 | 9/28/2020 - 10/4/2020 | 89 | 68 | 0 | 9 | 0 | 0 | 0 | 166 |
FEE-123 | 9/28/2020 - 10/4/2020 | 32 | 0 | 0 | 8 | 6 | 0 | 0 | 46 |
Below is the expected output data:
ID | Month | Total |
ABC-123 | August | 5 |
ABC-123 | September | 416 |
ABC-123 | October | 10 |
ABC-155 | August | 4 |
ABC-155 | September | 312 |
ABC-155 | October | 13 |
DDE-123 | August | 1 |
DDE-123 | September | 445 |
DDE-123 | October | 9 |
FEE-123 | August | 7 |
FEE-123 | September | 361 |
FEE-123 | October | 14 |
You'll see that I have split up the month data based on Time-Period column
and the values/total are also extracted based on the time-period as well.
Its a bit hard to explain but hopefully my sample data makes more sense.
Solved! Go to Solution.