How to split value based on month of date range

[hal_dal]

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.

[grazitti_sapna · Accepted solution]

Hi @hal_dal , try this. I hope this works for you.

Thanks.